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

HSDLee
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

Best Answer

  • rob_marshall
    edited November 2022 Answer ✓

    @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

    2022-11-10_08-24-44.png

     

    This is for the Lookup:

    2022-11-10_08-25-50.png

     

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

     

    2022-11-10_08-37-28.png

     

    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.

Answers

  • rob_marshall
    edited November 2022

    @zdlee 

     

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

  • ManjunathKN
    edited November 2022

    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
    edited November 2022

    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
    edited November 2022

    @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
    edited November 2022

    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
    edited November 2022

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

     

    2022-11-10_11-22-26.png

  • ShubhamCh
    edited November 2022

    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
    edited November 2022

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

     

    2022-11-10_12-49-41.png

     

    Does that help?

     

     

  • ShubhamCh
    edited November 2022

    @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
    edited November 2022

    @ShubhamCh 

     

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

  • ShubhamCh
    edited November 2022

    @rob_marshall 

     

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

     

    -Shubham