Moving Average multiple dimensions in a Chart

[beunderf]

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

[beunderf]

Please...anyone??

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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.