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?
Thank you
Solved!去解决方案。
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.
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()) THENMOVINGSUM(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.
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.