End of Month Totals in a Module.
How do I get end of Month Totals to show in a Module that is has a daily time dimension?
Best Answer
-
From the screenshot I think you are trying to sum the values of the preceding days into a predefined monthend day. The day you have highlighted indicates that 30/12 is the moth end date for December.
I will assume this is correct.
You will need to create a mapping which defines how you treat each day of the month and TIMESUM to define which range of days sums into this month end date.
The best way to do this is in a time system module set to days using a boolean line item to define which day is the month end date and a number formatted line item to determine the criteria feeding the TIMESUM. You may need to populate this mapping manually.
In your target module us an IF statement to determine if the date is a month end date, use TIMESUM to sum between the present date and the start of the period and where it is false refer to the data for that period.
The formula would look something like this; =IF NOT TimeSystemModuleDays.MonthEndDate THEN TargetModule.Value ELSE TIMESUM(TargetModule.Value, TimeSystemModuleDays.Start, 0, SUM)
I have used a NOT statement as the majority of dates will NOT be month end. TimeSystemModuleDays.Start should be a negative number as it represent the start position of the SUM and is in the past.
I would recommend that you break out the calculations. Use one line item to determine the day value and a second to sum it into the month end date.
Good luck, Chris
0
Answers
-
This is controlled by the Summary in the blueprint of each line item:
https://help.anaplan.com/en/9ab4e05a-1748-4027-****-525d5bdd5b22-Summary-Method
Jason
0 -
I do have my SUM selected for my Summary, but not adding up my EOM totals. I have a pic attached to show what I'm talking about.
Thank you
0 -
Do you have a filter of some kind applied, or are you hiding some of the items in the time dimension? looks like it jumps from 31-Jan to 4-Feb. There should be a JAN total for the month, and then also the 1st, 2nd, and 3rd of FEB displayed between those two days.
0 -
Please don't use TimeSum when the line item is dimensionalized by Time, that is a massive performance hit. TimeSum() should only be used when the source is dimensionalized by Time, but the target (result) is not.
In case you haven't seen this yet, please check out this youtube where Mark and myself have a little fun explaining this.
https://www.youtube.com/watch?v=PvimWuX5jVw
Rob
3 -
0
-
It depends. If they are wanting to sum the values for all of time, then timesum() without the Time in the Applies To is the way to go. The last part is the key! If they are wanting to sum only the monthtodate quartertoDate, or YearToDate, then use those formulas. If they are summing between two dates and the line item has Time in the Applies To, then MovingSum() is the way to go.
Rob
1
