Last 12 months SUM and Actual Version

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.

Accepted answers

nathan_rudman

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]

All comments

nathan_rudman

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

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

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&itemstyle=narrow

as well as LIne Item subsets:

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

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

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…