# Formula for comparison of previous quarters and months

Options

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

Tagged:

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

• Options
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
• Options
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.
• Options
Works perfectly, thanks a lot!
Mathias