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
Solved! Go to Solution.
IF MONTH(START()) = MONTH(END()) THEN Periodic Previous Month ELSE Periodic Previous Quarterand
IF MONTH(START()) = MONTH(END()) THEN YTD Previous Month ELSE YTD Previous Quarterrespectively. 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.