Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

ektarathi
New Contributor III

Adding a bar for 'Average' in bar chart


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%.)

1 Solution

Accepted Solutions
Not applicable

Re: Adding a bar for 'Average' in bar chart

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

View solution in original post

8 Replies
Not applicable

Re: Adding a bar for 'Average' in bar chart

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.

Not applicable

Re: Adding a bar for 'Average' in bar chart

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

View solution in original post

ektarathi
New Contributor III

Re: Adding a bar for 'Average' in bar chart

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

Highlighted
veidlburkhard
Contributor III

Re: Adding a bar for 'Average' in bar chart

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:

AvrgReferenceLine.jpg

Enjoy

Burkhard

ektarathi
New Contributor III

Re: Adding a bar for 'Average' in bar chart

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?

Not applicable

Re: Adding a bar for 'Average' in bar chart

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

Not applicable

Re: Adding a bar for 'Average' in bar chart

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.Screenshot_031714_015912_PM.jpg

ektarathi
New Contributor III

Re: Adding a bar for 'Average' in bar chart

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!