Formula for comparison of previous quarters and months

I am looking for a correct formula where, in a destination module, if I do a time selection on a specific month I get the periodic value and the ytd value of that month and the previous month. But in the same line item the formula should hold when I select a quarter. So when selecting a quarter it should give me the periodic value and the ytd value of that quarter and the previous quarter.   Currently the set up is the following:  

SOURCE MODULE TIME
Account Line Item jan feb  mar Q1 apr  may  june Q2
Sales Periodic 2 3 4 9 5 6 7 18
Sales YTD 2 5 9 9 14 20 27 27
DESTINATION MODULE Line item
Account Time

Periodic Actual

Periodic Previous

YTD Actual 

YTD Previous

Sales June 7 6 27 20
Sales Q2 18 15 27 20

[img height="81" width="491"]file:///C:\Users\mverbist\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png[/img]  

Formulas: Periodic Actual Source Module.Periodic
  Periodic Previous OFFSET(Source Module.Periodic , -1 , 0)
    LAG(Source Module.Periodic , 1 , 0)
  YTD Actual Source Module.YTD
  YTD Previous OFFSET(Source Module.YTD , -1 , 0) 
    LAG(Source Module.YTD , 1 , 0)

    The lag and the offset formulas refer to 1 period earlier on the timescale and they have the same behaviour. It works fine if you select a month but as you can see it does not give the correct values when selecting a quarter. The values indicated in red are wrong, they should both be 9.   So could anyone provide me with a correct formula? Or some solution how I can make this work?    Thanks!   Mathias

Best Answer

  • OK I see. In that case you need some intermediate line items:
    • Periodic Previous Month
    • Periodic Previous Quarter
    • YTD Previous Month
    • YTD Previous Quarter
    In the Previous Month line items you put the formulae you've currently got in Previous (i.e. LAG(Source Module.Periodic , 1 , 0) for Periodic Previous Month), and in the Previous Quarter line items you put the same formulae but lagging by three months instead of one (i.e. LAG(Source Module.Periodic , 3 , 0) for Periodic Previous Quarter).

    Then in Periodic Previous and YTD Previous you can decide which of the two to pick up by saying
    IF MONTH(START()) = MONTH(END()) THEN Periodic Previous Month ELSE Periodic Previous Quarter
    and
    IF MONTH(START()) = MONTH(END()) THEN YTD Previous Month ELSE YTD Previous Quarter
    respectively.

    You can now change the summary methods of these line items to Formula, and Anaplan will pick up either the previous month or the previous quarter as appropriate.

    I've attached the full blueprint in case this isn't clear. I've got it working using the attached.

Answers

  • Hi Mathias,

    Your formulae are all fine; the problem is your summary methods. If you change the Time Summary for YTD Actual and YTD Previous to "Closing Balance" then you'll get the results you want.

    To do this, go to the blueprint and click the ellipsis in the Summary entry for the YTD Actual line item. The main Summary is probably already to "Sum", which is fine; just change the Time Summary to "Closing Balance". Then do the same for YTD Previous.

    Hope this helps,

    Pete
  • Hi Peter,
    thanks for your repsons, unfortunately your answer is not correct. I already have the summary methods the way you describe them so that will not solve the problem.
    The problem is that if I select a quarter, the lag or the offset formula only looks 1 period back instead of 3. so somehow I should be able to differentiate between a quarter and a month in the formula but I have no knowlegd of such a formula. 
  • Works perfectly, thanks a lot!
    Mathias