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
ChrisHeathcote
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 Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

3 REPLIES 3
ChrisHeathcote
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 Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
ChrisHeathcote
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 Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

DMManalili
Frequent Contributor

Re: Average 3 months data

Thank you! 🙂