# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for
Did you mean:
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)

• ### Reference line

1 Solution

Accepted Solutions
MVP

Try this expression

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

Try this expression

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

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

Community Browser