Cumulative average

[#1]

Hello!

I have a straight table wth average of marks by month. Now I want to have the cumulative average of marks of the 6 last months. I can't do this in the loading script because average must be calculate according to the selections.

I tried this but it doesn't work : round(avg({$<[MONTH]={">=$(=above(MONTH,6))<=$(=above(MONTH,0))"}>}[MARK]),0.01)

Regards,

[pover · Accepted solution]

If the months are going to be visible, you can use the rangeavg() function.

rangeavg(above(sum(Mark),0,6))

The above(...,0,6) will return a list of the 6 last values including the value on the current row.

Karl

[#3]

Thank you, it works !

But if I select a month in my months list, I would like have the average calculated for the 6 previous month too. Is it possible ?

[pover]

Yes, it is possible, but you with have to change the script to add a "asof" table or a table island.  There are various posts on the subject, and even though they directly concern accumulating sums, you can use the same concept and apply it to rolling averages of the last 6 months.

Karl

[#5]

Thank you for this information Karl !

[maksim_senin]

Hi,

Don't you have a clue on how to calculate average sum of values (say, number of sold units) between monthes of several years?

For instance, we have the following table:

Unit     Date     Value

A     2010-01-01     1045

A     2010-01-02     23

A     2010-02-01     523

B     2010-02-02     54

A     2011-01-01     32

A     2011-01-02     2414

A     2011-02-01     53

B     2011-02-02     462

I'd like to calculate avg. sum of Value for unit A in the month 01 for all the years, i.e., avg. between 1045+23 and 32+2414, but not avg. between 1045, 23, 32, 2414.

Thanking you in advance.

[#7]

Another question : when I got some selections and not values for all months, my average is not good. Example :

period   mark

201109          7,5

201110          7,5

201110          10

201111          0

201112          7,5

201112          7,5

201112          7,5

201201          7,5

201201          2,5

201204          2,5

For the 201204 period I should have 5 but I got 6 (6 rolling months average). Qlikview take the last 6 months with values and not the 6 real months... I tried to check the 'show all values' box but it still not working 😞