Contributor

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?

6 REPLIES 6
Highlighted
Community Boss

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

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

 

Contributor

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

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?

Community Boss

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

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)

Contributor

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

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. 

Community Boss

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

@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

Contributor

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

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?