Contributor

## Forecast Data using TIMESUM function

Hi Team,

I have one situation, where I am calculating a Line-item in Forecast scenario using Sum of Last 12 months (from the Current Period). Need your inputs to help with the below model design.

Scenario 1:

Current period = Jan 2021

Actual Version = Jan 2020 till Dec 2020

Forecast Version = Jan 2021 till Dec 2021 (i.e. including current period)

Forecast_LI = TIMESUM(ACTUAL_LI, -12,-1) i.e. Sum of Actual Values from Jan-Dec '20 for all periods in Forecast Scenario as the Formula scope is Forecast.

Scenario 2: (when we change Current Period to Feb 2021)

Current period = Feb 2021

Actual Version = Jan 2020 till Jan 2021

Forecast Version = Feb 2021 till Dec 2021 (i.e. including current period)

Forecast_LI = TIMESUM(ACTUAL_LI, -12,-1) i.e. Sum of Actual Values from Feb'20 - Jan '21 for all periods in Forecast Scenario as the Formula scope is Forecast.

But what I want is:

For Jan 2021 Forecast Data it should still be:  Sum of Actual - Jan'20 till Dec'20 from Scenario 1.

and Feb 2021 - Dec 2021 (i.e. 2nd Scenario), it will be : Sum of Actual - Feb'20 till Jan'21.

Regards,

Jagmit

14 REPLIES 14
Contributor
Yes, I have Time as a dimension in my Target module.

I have attached the excel here for your reference.

Earlier I was using MOVINGSUM, but then requirement has been changed and Forecast values need to be as per the Excel shared.

https://community.anaplan.com/t5/Anaplan-Platform/Last-12-months-SUM-and-Actual-Version/m-p/93795#M2...
Community Boss

As I understand you need the sum different across time periods based on version.

Firstly, are you using native Anaplan versions?

I will assume that you are and base my recommendation on that.

You will need to add version to the inputs which inform the sum where you can differentiate the time periods in which to sum over by version.

However, I would not recommend using TIMESUM.

This should only be used where the target is not dimensioned by time as it will consume significant model resources and adversely impact the performance of your model.

Using MOVINGSUM would be more appropriate where the inputs update dynamically in relation to the current month.

Set up a current time versions system module containing just version as a dimension.

Here you can define the current period for each version.

In your target module dimensioned by time use this current period reference to calculation the starting and ending positions for each time period.

These will be used to feed the MOVINGSUM function.

Good luck,

Chris

Chris
HeathcoteAndHerran.com
Contributor
Hey Chris,

Thanks for the revert.
Unfortunately, cannot use MOVINGSUM, and have shared my excel mock-up above.
I am using 2 Versions:
Actual and Forecast (from Time Dimension- default)

I have Actual Sales Value = Jan'20 till Dec'20

I am populating Sales_Calculated = Jan'21 onwards in Forecast Scenario.
Community Boss

I am unsure why your requirement means you can not use MOVINGSUM.

As I outlined if you use an input module dimensioned by version to specify the current period you can use this reference to calculate the starting and ending positions in the calculation in the target. By analysing out the current period by version you can use different time ranges for each version.

Chris
HeathcoteAndHerran.com
Contributor

Hello All,

Thank you everyone for their valuable inputs and time spent into suggesting the best solution.

I am reviewing this with my team, and possibly might move from Current Month update frequency from monthly to quarterly.

Thank you!

Regards,

Jagmit