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

# Last "n" month averages in a bar chart

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:

[

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

];

• ###### Re: Last "n" month averages in a bar chart

May be this:

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;

[

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

];

• ###### Re: Last "n" month averages in a bar chart

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)) ?

• ###### Re: Last "n" month averages in a bar chart

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

• ###### Re: Last "n" month averages in a bar chart

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.

• ###### Re: Last "n" month averages in a bar chart

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