Last "n" month averages in a bar chart

[marcoyukon]

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

];

[sunny_talwar · Accepted solution]

May be this:

Sum({1<YearMonth={"$(='>=' & Date(AddMonths(Max(YearMonth), -3), 'YYYY-MM') & '<=' & Date(AddMonths(Max(YearMonth), -1), 'YYYY-MM'))"}>}Sales)/3

Where I changed the script for Qlik Sense to help it understand YearMonth as a date field

Sales:

LOAD Date(MonthStart(Date#(YearMonth, 'YYYY-MM')), 'YYYY-MM') as YearMonth,

  Product,

     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

];

[marcoyukon]

Thank you. Is it  also possible to make it work with a combination of aggr (), rangeavg() and above(), similar to the use on the table but now using the moving averages in chart widget instead ? i.e: rangeavg(above(SUM({1}Sales),1,3)) ?

[sunny_talwar]

You can, but why do you want to make your calculation more complicated and less efficient?

[marcoyukon]

No, I think you solution is great and I'll adapt to use it. I was just curious since that's how I calculate in tables, leveraging the rangeavg() funcition, and above() with an offfset.

[sunny_talwar]

If you insist

Only({1<YearMonth={'$(=vCurrentYearMonth)'}>}Aggr(RangeAvg(above(SUM({1}Sales),1,3)), Product, YearMonth))

But trust me, you don't want to use this