Bar chart with Reference line for previous year average

BlondeThursday — Sat, 16 Nov 2024 04:58:16 GMT

I am trying to create a bar chart that counts occurrences per month in the selected year. The customer also wants to have a reference line showing the average for the entire previous year as well. So basically what you would get if you tick the Average trendline box but for the previous year.

So, if the data looks like this: (Reference is unique)

DateIssued	Reference	Category
1/1/17	1	Basic
3/1/17	2	Complex
5/1/17	3	Basic
5/1/17	4	Basic
6/1/17	5	Moderate
8/1/17	6	High
8/1/17	7	High
21/1/17	8	Moderate
3/2/17	12	Basic
3/2/17	34A	Complex
3/3/17	12/5	Basic
6/7/17	ASC36	Moderate
6/7/17	72	High
12/7/17	RRP	Basic
12/11/17	Ri32	Niggling
3/1/18	18-a	Basic
14/1/18	18-b	Basic
14/1/18	18-c	Complex
14/1/18	18-d	High
3/3/18	18/5	Basic
4/3/18	TT3	High

We want Dimensions of Month and Category and an Expression of =Count(Reference).

But also a reference line that, for instance, would be horizontal at .5 (the average count per month for 2017) if the year selected was 2018 and the Category selected was 'Basic'.

I suspect I'm being a bit thick here, but I can't seem to see it. Probably having trouble with aggregating Count for set analysis of where Year = Year-1.

Any steer would be a help. Thank you.

Re: Bar chart with Reference line for previous year average

sunny_talwar — Thu, 13 Dec 2018 14:29:19 GMT

Try this expression

Sum({<Year = {$(=Max(Year)-1)}>} TOTAL Aggr(Count({<Year = {$(=Max(Year)-1)}>}Reference), MonthYear))/12

Re: Bar chart with Reference line for previous year average

BlondeThursday — Thu, 13 Dec 2018 14:37:47 GMT

Thank you very much. I was on the right track but getting muddled with the set analysis.

topic Re: Bar chart with Reference line for previous year average in QlikView

Bar chart with Reference line for previous year average

Re: Bar chart with Reference line for previous year average

Re: Bar chart with Reference line for previous year average