Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BlondeThursday
Contributor III
Contributor III

Bar chart with Reference line for previous year average

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.

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

Try this expression

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

View solution in original post

2 Replies
sunny_talwar

Try this expression

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

View solution in original post

BlondeThursday
Contributor III
Contributor III
Author

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