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

Accepted answers

MarkWarren

I'm not entirely sure I understand what's needed.
Here's what I have interpreted it as:

So we do a different TIMESUM for each period, unless you don't have data 12 months in advance of that in which case it shows the previous value.
Kind of matches your spreadsheet...

All comments

nathan_rudman

I'm not very clear, but I'd say you want to use LAG(actual,12,0)

https://help.anaplan.com/en/b10966f6-334f-4eb4-8c24-9a31f6c785ba-LAG

Jagmit

Hi Nathan,

Thanks for the quick revert.

No, LAG won't work here as I am looking for Sum of Last 12 periods with respect to the Current Month.

But if we change the Current Month i.e. shift from Jan'21 to Feb'21, my Forecast Line Item will calculate new values and will over-write previous Forecast values when Current Month was Jan'21 for the Jan'21 period.
So, in brief:
Current Year Months = Jan'21 till Dec'21: The Forecast values shouldn't change for (historical current year months)

Misbah

@Jagmit

Why don't you use MOVINGSUM. If you have to sum the values for last 12 months based on the month you are in, it should be able to do it. Unless I am missing something.

TIMESUM should be used when you don't have Time as a dimension in your target module. It seems you have Time in the target module

Jagmit

Hi Misbah,

Thanks for the inputs.

I am attaching an Excel here, maybe that will help explain the Problem statement.

I can't use MOVINGSUM here, as the Forecast Month values i.e. (Current Month and onwards) the values need to be same.

Forecast_using_TimeSum_95395.xlsx

Jagmit

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#M21166

ChrisAHeathcote

@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

MarkWarren

Jagmit

This is close, only thing different here is the Sales_calculated line-item to be populated Jan 21 onwards.(i.e. Current Year Months)

MarkWarren

You could add a Timerange with the extra year in the future (FY22) and this would work. Hide that future year on the grid.
This allows you to do the ITEM(Time) + 12 for Current Period (or set manually) for all of FY21

Jagmit

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.

Misbah

@Jagmit

You can use @MarkWarren solution and use OFFSET in another line item to push the values to 2021

ChrisAHeathcote

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

Jagmit

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

@MarkWarren @ChrisAHeathcote @Misbah

Thank you for your inputs. I am attaching the solution we have decided to go for 'Monthly' update of Current Period.

I would only need End Period for lookup and also using MOVINGSUM and 1 Calculated LineItem, as everyone suggested to go for it when it was all figured out.

Also,

I am trying to figure out a solution for Rolling Forecast 'Quarterly' update of Current Period, to have the flexibility to choose the frequency of Current Period update. (Please see attached Excel)

Please let me know your inputs.

Thank you!

Regards,

Jagmit

Calculated_95651.jpg

Rolling_Forecast_Quarterly_95651.xlsx

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…