Average 3 months data

DMManalili
Frequent Contributor

Average 3 months data

Hi,

 

For your assistance please. I want to get the average of the past 3 months, however, the average result should be post to the remaining months. In the screenshot below, I created three lines items:

Commission % - input data

Latest 3 Months Average (Comm%) - I used this formula MOVINGSUM(Commission %, -2, 0, AVERAGE)

Previous 3 Months Average (Comm%) -  please see formula in the screenshot.

 

How can I post the 5% (average of Jan, Feb, Mar) for the month of April to Dec onwards?

 

DMManalili_0-1620809553245.png

Thank you 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisAHeathcote
Community Boss

Re: Average 3 months data

@DMManalili 

If you are not use Current Period then you can LOOKUP the average and post this forward.

For this you will need to create a module containing no dimensions or time. Add a time period formatted line item and use this to inform the LOOKUP.

Chris
HeathcoteAndHerran.com

View solution in original post

3 REPLIES 3
ChrisAHeathcote
Community Boss

Re: Average 3 months data

@DMManalili 

I assume that there is a cut off point from which you want all future periods to be populated with the three month average. I will for this post assume that you are using current period.

If so you can use 

=IF ISCURRENTPERIOD(END()) THEN MOVINGSUM(Commission %, -2, 0, AVERAGE) ELSE PREVIOUS(Previous 3 Months Average (Comm%))

However, I would recommend that you use a time system module to query the Current Period as this will be useful in other formulas.

Chris
HeathcoteAndHerran.com
ChrisAHeathcote
Community Boss

Re: Average 3 months data

@DMManalili 

If you are not use Current Period then you can LOOKUP the average and post this forward.

For this you will need to create a module containing no dimensions or time. Add a time period formatted line item and use this to inform the LOOKUP.

Chris
HeathcoteAndHerran.com
DMManalili
Frequent Contributor

Re: Average 3 months data

Thank you! 🙂