12 Replies Latest reply: Jun 5, 2014 2:39 AM by Fanie Becker

# rolling 3 month average for past 6 months

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

• ###### Re: rolling 3 month average for past 6 months

Fanie,

1 approach i know is with island table and if in expression:

• ###### Re: rolling 3 month average for past 6 months

and 2'nd with intersection and set analysis

• ###### Re: rolling 3 month average for past 6 months

Thank you Dariusz

• ###### Re: rolling 3 month average for past 6 months

Dariusz do you have an example of intersection and set analysis?

Fanie

• ###### Re: rolling 3 month average for past 6 months

Examples are attached above....

Can't you see them?

• ###### Re: rolling 3 month average for past 6 months

ohh, i'am sorry, no i can see, that i attached the same twice. give ma a moment

• ###### Re: rolling 3 month average for past 6 months

I exchanged set example

• ###### Re: rolling 3 month average for past 6 months

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

• ###### Re: rolling 3 month average for past 6 months

Fenie,

use script like this:

data_tbl:

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

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

• ###### Re: rolling 3 month average for past 6 months

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.