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

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

Accepted answers

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.

All comments

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

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:

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

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.

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

Quick Links

Can you help?

Unanswered questions

Can we disable Create page button from the UX page
I want to disable the [create page] button for the end user from the dashboard. The end user can create a page or worksheet to link to the module to edit the data. can anyone please give some suggestion? Thx…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…