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



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?

Best Answer

  • anirudh
    Answer ✓

    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)


  • 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'





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

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

  • @kdoan 


    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:


    I hope that helps


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