Last 12 months SUM and Actual Version

[Jagmit]

Hi,

I am trying to workout a formula for Forecast Version Line Item. The requirement will be as given below.

Source Module: Source Line Item - Sum of  "Sales" of last 12 months in Actual Version

Target Module: Target Line Item- "Sales" for 1 period in Forecast Version.

Eg. Assuming I am forecasting Sales in Feb 2021; So the result should be = Sum of Actual Sales from Feb 2020 till Jan 2021.

It is needed to get some ratio calculated for further calculations in Forecast version itself.

Any help will be appreciated.

[nathan_rudman · Accepted solution]

for the version part, it really depends how you have built your model.

If you have built it with native Anaplan versions, then you should simply have the switchover on your forecast in which case the forecast contains the actuals already. If you can't have the switchover you'll have no choice but to [SELECT:Versions.actuals]

For doing the last 12, Id' use the formula MOVINGSUM like this:

movingsum = MOVINGSUM(module.line item, -12, -1,SUM)

do this in your source module

then in your target module:

source module.movingsum[select:versions.actuals]

[Jagmit]

Okay I did something similar, like creating a new line item to capture the Last twelve months using Movingsum function.

And then using Select to fetch the information in Target.

I was hoping to avoid the extra lineitem in Source Module, only to store last 12 months data for existing lineitem.

Regards,
Jagmit

[nathan_rudman]

you could probably combine it if it was taking huge space, but it is better to split the formulas, for performance. Also, you might be re-using the movingsum result some other place in the future and that means you won't have to write back the formula.

[Jagmit]

Thanks Nathan!

I am new to Anaplan community.

Is it possible to request Anaplan for best way to resolve this ? If not, can they consider this an idea for future releases?

I do want someone to validate and correct/guide me, if I can improve on solution design.

Regards,

Jagmit

[Jagmit]

I am forced to re-consider, as the number of line-items for which the same calculation has to be done is around 10+.

So, if we have number of original line-items = 10
Same number of line item to get 'sum of past 12 months' into 1 Forecast month = 10
Total line items = 20

Hence, looking for better way to achieve this.

[rob_marshall]

@Jagmit 

I am confused why you think you will need 20 line items.  Are you taking the movingsum for 10 different line items?  If this is the case, then simply create a line item subset of those 10 line items, and in a CALC module, create your movingsum based off the line item subset with the same dimensionality (versions, time, Product???, and the line item subset).  

@nathan_rudman is absolutely correct in this for a native versions standpoint and using switchover.  If you are using the version and switchover, when you are looking at Forecast, the previous Actuals will be rendered and be part of the movingsum.

Take a look at this video, Level 1, Lesson 3

https://learning.anaplan.com/totara/catalog/index.php?catalog_fts=switchover&orderbykey=score&itemst...

as well as LIne Item subsets:

https://learning.anaplan.com/mod/scorm/view.php?id=13543

Rob

Rob

[Jagmit]

Hi Rob,

Thank you for your revert.

I follow what you're suggesting, will surely try Line-item subset and post my update here.
Dimensionality is something like :
versions, time, Product,Channel, Market type and the line item subset).

Can't use native versions and switchover for this requirement.

Thanks for helping!

Cheers,
Jagmit