topic Moving Average multiple dimensions in a Chart in QlikView

Moving Average multiple dimensions in a Chart

beunderf — Fri, 18 Jan 2013 10:33:09 GMT

Hello,

I have a small problem.

In my example I have two products (A and B). They get a score every year. Now I want to calculate the moving average (4 years) score for each product.

I use the following expression in a Pivot table:

RANGEavg(before(AVG(Score),0,4))

Works perfect!

Now I want to get the same results in a Chart. But it doesnt seem to work. Any ideas?

All help is welcome 🙂

Frank

Re: Moving Average multiple dimensions in a Chart

beunderf — Sun, 20 Jan 2013 13:37:43 GMT

Please...anyone??

Re: Moving Average multiple dimensions in a Chart

Wed, 27 Mar 2013 12:26:05 GMT

Hi Frank,

I am having the same problem. Did you already found any solution? The ABOVE won't work with the selection of the last year. It is in QV10 possible with showing only the last 4 values. It seems they removed it QV11. Why?

Regards,

Patrick

Re: Moving Average multiple dimensions in a Chart

Wed, 27 Mar 2013 12:40:23 GMT

But the Above won't work if you want to show only tha last year. Please look at my example. In the example of Frank it will work.

Re: Moving Average multiple dimensions in a Chart

Mon, 15 Apr 2013 11:25:09 GMT

A possible solution by set analyis is, with use of variabels voor current month (CM), last year (LY) and current year (TY):

=if(D.Month<=CM,

(RangeAvg(Bottom(SUM({<sub={A},D.Year={$(=LY)}D.Month= >} Sales)*((12-RowNo())/12))

+(RangeAvg(top(SUM({<sub={A},D.Year={$(=TY)},D.Month= >} Sales)*(RowNo()/12))

)

D.Month is the dimension (months in this case)

This will calculate the moving average of the expression 'SUM({<sub={A},Dim.Jaar={$(=LY)},Dim.Maand= >} Sales)' for the past 12 months. It does so by selecting the months before the current month, including the current month itself, of TY and selecting the months after the current month of LY. To get the correct values, the range average of the expression is calculated for both periods and a weighted average is calculated of the two outputs.

It has however the disadvantages that (1) to make it insensitive for date selections, these fields have to added to the set analysis (in the example above, done voor Dim.Maand). (2) it does not work if a part of the data is nonexistent, all the months used have to be filled with data.