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

kdoan
kdoan
in Modeling

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?

Tagged:

Best Answer

  • anirudh
    anirudh
    Answer ✓

    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)

Answers

  • anirudh
    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
    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?

  • kdoan
    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. 

  • DavidSmith
    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
    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? 

Categories