Forecast Data using TIMESUM function

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

 

Kindly advise.. Thanks !

 

 @rob_marshall @DavidSmith @Misbah  

 

 

Regards,

Jagmit

 

 

14 REPLIES 14
Jagmit
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.

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

@Jagmit 

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.

MOVINGSUM 

Good luck,

Chris 

Chris
HeathcoteAndHerran.com
Jagmit
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.
ChrisAHeathcote
Community Boss

@Jagmit 

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
Jagmit
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