Rolling average formula
I'm currently trying to create a formula to calculate the 3 week rolling average, the reason we're doing it in formulate rather than through the context calculations is because it will form part of a wider measure list.
I wrote this formula to try and keep the first two line of data blank, so the rolling calculation starts lagged, however it doesn't seem to be working. Is there anything i've missed?
3 replies
-
Hi
The example above is using MDX (as Im assuming it is connecting to an MS tabular/olap model with the use of the IIF) , instead of trying to see where Lag(1) = 'NULL' you can use the expression IsEmpty() . As the lagged field is empty (it doesn't exist as a dimensional combination for the metric) this will detect and give you the functionality you require.
so in my example I have this as my formula
which returns this
I hope this helps
thanks
Mark.
-
@Mark Oldfield,
Works well, but it will become cumbersome when looking at wider rolling average ranges. It is also very static in terms of how the user to interact with it. It is easier, and imho, better, to use the RANGE operator in MDX, ":" to define the range over which to calculate the rolling average:
this now also then lends itself to being able to dynamically change the range over which the rolling average is performed.
Create a global numeric parameter, in this case called Range and in this case providing values from 3 to 9 in steps of 1:
Then use this to drive the Lag() function and the divisor of the SUM(Range) expression:
The Range parameter allows the user to select how many periods to use for the rolling average. We need to subtract 1 from this to define the Lag() amount.
This results in the user being able to select the number of periods that make up the rolling average.
In this case, the default, 3:
In this case, using the slider (red box) to choose 6:
Hope that helps.
Ian
-
That's very helpful thank you!
-