rolling 3 month average for past 6 months

[#1]

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

[#2]

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

[#3]

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

[#4]

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