End of Month Totals in a Module.

neg177
Regular Contributor

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?

7 REPLIES 7
jasonblinn
Master Anaplanner/Community Boss

Re: End of Month Totals in a Module.

This is controlled by the Summary in the blueprint of each line item:

jasonblinn_0-1610487341209.png

https://help.anaplan.com/en/9ab4e05a-1748-4027-babe-525d5bdd5b22-Summary-Method

 

Jason

neg177
Regular Contributor

Re: End of Month Totals in a Module.

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 

jasonblinn
Master Anaplanner/Community Boss

Re: End of Month Totals in a Module.

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. 

 

jasonblinn_0-1610489284246.png

 

ChrisHeathcote
Community Boss

Re: End of Month Totals in a Module.

@neg177 

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 

rob_marshall
Master Anaplanner/Community Boss

Re: End of Month Totals in a Module.

@ChrisHeathcote 

 

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

Function vs. Function:MOVINGSUM vs. TIMESUMWe're back with round 2 of Function vs. Function! This time, join Anaplan Live! host Chris Weiss as he moderates a...
ChrisHeathcote
Community Boss

Re: End of Month Totals in a Module.

@rob_marshall 

Of course! Thanks for the heads up.

Would you therefore recommend using MOVINGSUM?

 

rob_marshall
Master Anaplanner/Community Boss

Re: End of Month Totals in a Module.

@ChrisHeathcote 

 

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