5 Replies Latest reply: Feb 27, 2017 3:16 PM by Sunny Talwar RSS

    Last "n" month averages in a bar chart

    Marco B

      I am trying to combine the use of "rangeavg" and "above" to

      calculate the average Sales for last 3 months.

       

      I can get it to work on a table using rangeavg(above(SUM({1}Sales),1,3))

      but can't get the calculation to work in the Actual vs Avg bar chart

       

      What would the expression in the chart would need look like ?

       

      Somewhere in the bar chart measure expression:

       

      " rangeavg(above(SUM({1}Sales),1,3)) "

       

      would have to include:

       

      " {1<YearMonth={'$(=vCurrentYearMonth'}>Sales} " ??

       

      or possibly use a different approach in the bar chart to get the averages for each datapoint ?

       

      Screen shot and sample .qvf file are attached.

       

      Sales:

      Load * inline

      [

      YearMonth,Product,Sales

      2016-03,A,120

      2016-03,B,350

      2016-04,A,120

      2016-04,B,350

      2016-05,A,120

      2016-05,B,350

      2016-06,A,120

      2016-06,B,350

      2016-07,A,200

      2016-07,B,80

      2016-08,A,160

      2016-08,B,240

      2016-09,A,180

      2016-09,B,200

      2016-10,A,240

      2016-10,B,260

      2016-11,A,180

      2016-11,B,190

      2016-12,A,150

      2016-12,B,160

      2017-01,A,100

      2017-01,B,50

      2017-02,A,200

      2017-02,B,250

      ];