# How can I get the average of months for prior year?

Options
edited November 2022 in Modeling

I would like to get the averages for a line item using prior year month values. I can use the formula below but I don't like the idea of hardcoding. Is there another way I can do this without hardcoding?

The current formula I have is: LAG(<source LI>,12,0)+LAG(<source LI>,24,0)+ LAG(<source LI>,36,0) / 3

Tagged:

• edited November 2022 Answer ✓
Options

Good call and that is what I get when I reply from my phone...  It can be done using MovingSum, but the LAG works a bit better/faster.

Here are my system modules:

This stores the offsets

This is for the Lookup:

The was done on a very small set of data, only 2.8 Million cells.

MovingSum:

MOVINGSUM(Data, -SYS MovingSum Global.LAst Year, -SYS MovingSum Global.LAst Year) + MOVINGSUM(Data, -SYS MovingSum Global.'2 Years', -SYS MovingSum Global.'2 Years') + MOVINGSUM(Data, -SYS MovingSum Global.'3 YEars', -SYS MovingSum Global.'3 YEars')

LAG():

LAG(Data, SYS MovingSum Global.LAst Year, 0) + LAG(Data, SYS MovingSum Global.'2 Years', 0) + LAG(Data, SYS MovingSum Global.'3 YEars', 0)

LAG with STRICT:

LAG(Data, SYS MovingSum Global.LAst Year, 0, STRICT) + LAG(Data, SYS MovingSum Global.'2 Years', 0, STRICT) + LAG(Data, SYS MovingSum Global.'3 YEars', 0, STRICT)

Lookup:

Data[LOOKUP: 'SYS Time Filter - Month'.PY] + Data[LOOKUP: 'SYS Time Filter - Month'.'PY-1'] + Data[LOOKUP: 'SYS Time Filter - Month'.'PY-2']

Timings:

Movingsum: 83 ms

LAG: 47 ms

LAG with Strict: 42 ms

Lookup: 217 ms

Again, very basic test, but it looks like in this instance will work best with LAG.  So, @zdlee going back to your original question, it looks like LAG is in fact the best way.  I would just use a Global module for the offsets, but it is not mandatory.

• edited November 2022
Options

You might want to try MovingSum(), it was specifically made for calculations like this.

• edited November 2022
Options

How can we achieve this by movingsum?. I believe his ask is to average the previous 3 years month wise(according to his present logic). it should be values (Jan 19+Jan 20+Jan 21)/3 in Jan 22.

Thanks,
Manjunath

• edited November 2022
Options

Firstly, thank you so much for doing this. Appreciate so much for providing the timings to help and understand the performance of each function in this scenario.

Why is Lag with strict is performing better than Lag alone. Strict is another parameter added to lag and I think it will create one more thread for checking.

Thanks,
Manjunath

• edited November 2022
Options

Because LAG() without Strict or SemiStrict can go either forwards or backwards based on the parameter...Strict and SemiStrict only has to account for one direction.

• edited November 2022
Options

How these timings are calculated and what do they represent? Do they represent model opening time or calculation time?

- Shubham

• edited November 2022
Options

They are the full calculation time in ms, to get the seconds, you divide by 1000.  Here is the trick, I removed the formula, then pasted it back in...Then looked at splunk for that serial number (you can find the serial number in History).

• edited November 2022
Options

I see the method that you used to record time of calculation but I have my doubts here.

1. Lets say you wrote a formula in a line item at 12:00:00 (ID:-1). Now you want to record the time of calculation, so you remove this formula at 12:01:00 (ID:-2), pasted it back at 12:01:20 (ID:-3). As I understand the history timestamps, ID:-3 shows the time when you pasted the formula but not the calculation time of that formula. (I may be wrong here)
2. As this method of calculating time includes human steps (removing and pasting), what if you pasted the formula 1 sec late. Will that second not be counted between two timestamps?
3. The internet may also play a role here. For example the changes were not recorded right away due to low internet speed and it took some time to register those changes, which I think, is also counted between timestamps.

I may be completely wrong here about the concept of history timestamps. Please clarify these doubts.

Thanks!

-Shubham

• edited November 2022
Options

I think you misunderstood my post regarding the serial numbers....The timings have nothing to do with subtracting the timestamps, the serial numbers told me which transaction to look for in Splunk, the logging system behind Anaplan.

Does that help?

• edited November 2022
Options

Yeah, I totally misunderstood the previous reply. Thanks a lot for the clarification. How can I see the Splunk dashboard for my own model?

• edited November 2022
Options

Currently, you can't so you can safely say that I cheated since I have access to toys that others don't.

• edited November 2022
Options

Hahaha, no problem. I learned something new today. All thanks to you!

-Shubham