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

HSDLee

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

rob_marshall

@ManjunathKN 

 

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.

rob_marshall

@zdlee 

 

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

ManjunathKN

Hi @rob_marshall 

 

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

ManjunathKN

Hi @rob_marshall,

 

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

rob_marshall

@ManjunathKN 

 

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.

ShubhamCh

Hi @rob_marshall 

 

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

 

- Shubham

rob_marshall

@ShubhamCh 

 

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).

 

ShubhamCh

Hi @rob_marshall 

 

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

rob_marshall

@ShubhamCh 

 

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?

 

 

ShubhamCh

@rob_marshall 

 

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

rob_marshall

@ShubhamCh 

 

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

ShubhamCh

@rob_marshall 

 

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

 

-Shubham