How to get the average of the last 3 month from the same line item for Forecast version?

Hi,

I am doing an OpEx forecast. How do I write a forumula to get the average of the past 3 month from the same line item for the Forecast Version only? I also want to be able to override the amount manually or by upload if needed. Is this possible or do I have to create another module to link it?

Screen_Shot_2019-06-23_at_4_47579.png

Accepted answers

anirudh

Sure!
Try this in the 3 months average: IF MOVINGSUM(Upload Amount, -2, 0, AVERAGE) = 0 THEN POST('3 Months Average', 1) ELSE MOVINGSUM(Upload Amount, -2, 0, AVERAGE)

All comments

anirudh

Hi,

You cannot have a formula and a data entry line item simultaneously. So I would suggest creating a line item called '3 month average' with the following formula; this is to get the average of the last three months:

MOVINGSUM(Amount, -2, 0, AVERAGE)

And then create a second line item called 'Override' which is the data entry line item. Finally, create a third line item called 'Final average forecast' with the formula: IF Override <> 0 THEN Override ELSE '3 Month Average'

Regards,

Anirudh

kdoan

Thank you. It almost work but the moving sum formula ended in June since the last 3 months trail off. Any idea on how to push the forumula until the end of the year or to what ever time length I want without me adding the amount manually?

3_months_average_47600.PNG

anirudh

Sure!
Try this in the 3 months average: IF MOVINGSUM(Upload Amount, -2, 0, AVERAGE) = 0 THEN POST('3 Months Average', 1) ELSE MOVINGSUM(Upload Amount, -2, 0, AVERAGE)

kdoan

Thank you so much. It worked. I have one last question.

Is there any way to apply to different formula to the same line item? For example, on my Amount line item, its the final amount i want to show. I want the number to be exactly the same and not calculated for my actual and the first 3 month. I tried using switch over but it still calculate my actual when I switch to forecast.

Screen_Shot_2019-06-24_at_9_47709.png

DavidSmith

@kdoan

Yes

there is a little known technique that works for numeric values

You create a line item subset and then you can specify a different formula by version

The details can be found here:

https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Add_Version_Formula.htm?

I hope that helps

David

kdoan

Thank you for pointing me in the right direction. I have another question, do I need to create another module to bring the final amount in? For Example, I created a Line item subset call Final Amount. I tried to make it equal Upload Amount in the Actual but I keep getting an error.

Do I need to create another module to do the calculation?

Screen_Shot_2019-06-24_at_1_47723.png

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
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…