4 Replies Latest reply: Apr 15, 2013 7:25 AM by Koen Frankhuizen RSS

    Moving Average multiple dimensions in a Chart

    Frank Beunder

      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

          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
              Koen Frankhuizen

              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.