Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to calculate the rolling 3 month average for a key performance indicator.
1. In the expression, how do I select the current month and months -1, ... -6;
2. How do I calculate the rolling avg for these months.
Will appreciate
Fanie
Dariiusz - I'm testing on a demo package, hence cannot open the .qvw files. Any way you have this is a doc or mayb the expression statement?
Will appreciate
Fanie
Fenie,
use script like this:
data_tbl:
LOAD * INLINE [
month, val
1, 1
2, 3
3, 2
4, 4
5, 6
6, 5
7, 2
8, 3
9, 4
10, 8
11, 7
12, 1
13, 2
14, 12
15, 3
];
for i = 0 to 9
load month as month_dim,
month-$(i) as month,
$(i) as diff
Resident data_tbl Where month >= $(i);
NEXT;
and chart with:
dimension:
month_dim
expressions like:
3 months rolling:
avg({<diff={"<=3"}>} val)
6 months rolling:
avg({<diff={"<=6"}>} val)
etc
regards
Darek
Thank you Darek
What I have done, since I load all the transaction data and have more than 6 months data, is the following:
rangesum(above(count({$ <[Line Status]={'X'}>}[Line Status]),0,3))/3)/
((rangesum(above(count([Line Status]),0,3))/3)-
(rangesum(above(count({$ <[Line Status]={'P'}>}[Line Status]),0,3))/3))
to calculate the 3 Month Rolling Average of Complete Deliveries.
Comments?
Regards
Fanie