topic Cumulative average in QlikView

Cumulative average

Thu, 09 Feb 2012 16:57:31 GMT

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,

Cumulative average

pover — Thu, 09 Feb 2012 17:21:42 GMT

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

Cumulative average

Fri, 10 Feb 2012 08:02:17 GMT

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 ?

Cumulative average

pover — Sun, 12 Feb 2012 16:56:00 GMT

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

Cumulative average

Mon, 13 Feb 2012 07:57:00 GMT

Thank you for this information Karl !

Cumulative average

maksim_senin — Wed, 11 Apr 2012 02:17:21 GMT

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.

Cumulative average

Tue, 05 Jun 2012 08:12:51 GMT

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

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 😞