Adding a bar for 'Average' in bar chart

[ektarathi]

I have a bar chart with two dimensions(Area and Year), and one expression Avg(Attainment).I need to one more bar for each Area , which will show the Avg(Attainment) over years in that area.Please refer to the attachment.I am looking for something exactly like this.Any help is greatly appreciated.

(In my qlikview object I have used bar offset and reference line to get the bars above and below 100%.)

[#2]

Hi Ekta,

I have found that with adding an average line to my line charts I need to overlay a second "transparent" line chart over top.  This will work for your bar chart as well but it is not ideal.  If anyone has a better method I would also be interested.

[#3 · Accepted solution]

TRy to use only Area as dimension and 4 expressions or more, depends by number of year...

ex.

label= max(Year)

1)avg({<Year={$(=max(Year))>}Attainment)

label= max(Year)-1

2)avg({<Year={$(=max(Year)-1)>}Attainment)

label= max(Year)-2

3)avg({<Year={$(=max(Year)-2)>}Attainment)

label= '3yr avg'

4) avg(Attainment)

HOpe this helps

[ektarathi]

Thanks a lot Mario!!!The solution worked 🙂 🙂

[veidlburkhard]

Hi Julian,

a much easier way would be to add a reference line into your bar chart. Go to the 'Presentation' tab and click on 'Add' in the 'Reference Line' field (bottom right). Here you can key in your expression for your average and also a label to this line. See the example below:

Enjoy

Burkhard

[ektarathi]

Hi Mario,

I am facing one problem while I implemented this approach.I have multibox for 'Area'(with dropdown as available Area's) in my application.Whenever I select an Area from dropdown in multibox...which does not have data for all the 3 years(say the selected area has attainment data just for 2013(and not for 2011 and 2012)), the entire graph blanks out showing "No data to Display", wherein I would like it to show  two bars, one for 2013 and one for Avg(which will eventually be same as 2013).Any idea what can be done to resolve this?

[#7]

TRy to change the 4th AVG expression in:

rangeavg(column(1),column(2),column(3))

and uncheck in presentation tab of the graph suppress missing flag.

hope this helps

[#8]

Hi Burkhard,

Thanks for the reply but what I am trying to achieve is a little different.  I want the average of the second dimension for each point of the first.  See attached.

[ektarathi]

Hi Mario,

I already used rangeavg and unchecked supress missing, but it was not working.But then I added else clause as '0'.As in if there is no data for a given year, then the bar should have '0' as value.This solved my issue.I think the issue was because I was getting null and not zero, and hence graphs were somehow getting supressed.

Thanks a lot for all your help on the issue!