## Target lines on a bar chart using error bars

During my internship at Bitmetric one of my project goals was to build a QlikView application to benchmark logistical companies. I needed to visually compare the results of one organization with the average results of all organizations. My first try was using a combo chart with symbols, but I wasn’t happy with how that looked:

The dot seems to reflect an interval instead of a value. What I really wanted was to make is a chart that uses a straight line for the benchmark:

In this blog I will show you how you can make such a chart.

### The case

Suppose there are four people selling four different products. You want to know how each person’s sales compares to the average sales per person. I have created two variables to store these expressions:

• Average products sold; eAvgSold  = sum({1} #Sold) / count({1} distinct Person)
• Product sold per vendor; ePersonSold = sum(#Sold)

### The standard Combo chart

Initially, I built a combo chart in QlikView to compare the average number of products sold (stored in the variable eAvgSold) to the number of products sold by a person (stored in the variable ePersonSold):

Instead of a dot, you can also choose other symbols, such as crosses, circles or squares. But you can’t choose a horizontal line.  It’s better to have a horizontal line instead of a dot; a dot reflects an interval instead of a value.

When I went to the Masters Summit in Amsterdam, Christof Schwarz did a QlikView tips and tricks presentation. One of the tips he showed was how to make a bar chart using Error Bars as target lines. I used this solution to create the chart that I needed.

### Error Bars

‘Error bars are a graphical representation of the variability of data and are used on graphs to indicate the error, or uncertainty in a reported measurement.’ (source: Wikipedia)

An error bar consists of three elements: an error bar below, an error bar above and a connecting line. The connecting line indicates the interval of the error. The location of the error bars is calculated relative to the bar chart value. In the image below, the value of the bar is 35 and the error (both above and below) is 15:

We want a single horizontal line on each bar the show a person’s sales. By default, error bars show two horizontal lines with a connecting vertical line. However, if we place both error bars at the same location, the connecting line disappears.  To achieve this, we need to adjust the formulas for both error bars so that both lines will appear in the same location:

• Error Bar Above: eAvgSold + error  = eAvgSold + (ePersonSold – eAvgSold) = ePersonSold
• Error Bar Below: eAvgSold – error  = eAvgSold – (eAvgSold– ePersonSold) = ePersonSold

### Using Errorbars for horizontal lines: step by step

If the expression above seems a bit complicated, follow the next steps to create your own graphs with error bars.

1. Load the data, the fields ‘Product’, ‘Person’ and ‘#Sold’

• Settings » Variable Overview » Add Variable
• Add variable eAvgSold  = sum({1} #Sold) / count({1} distinct Person)
• Add variable ePersonSold = sum(#Sold)

3. Make a bar chart

• Dimension: Product
• Expression: \$(eAvgSold)

4. Define the error bars

• Expression tab » Display Options » Select ‘Has Error Bars’
• Insert the error bar formulas at the expression
• Error Bar Above: -\$(eAvgSold) + (\$(ePersonSold)
• Error Bar Below: \$(eAvgSold)  \$(ePersonSold)

• Improve the presentation of the error bar at the presentation tab

5. That’s it! You have now a beautiful chart with horizontal lines to make a comparison.

Blog posts from the Bitmetric team. At Bitmetric, Qlik is what we do. We've been doing it for years, and we can do it for you! ;)

• 1
Navdeep
February 20, 2015 - 13:33 | Permalink

Good option.

• 2
Hennie
February 20, 2015 - 13:39 | Permalink

This is an excellent trick! Thanks for sharing.

• 3
Faruk
February 26, 2015 - 15:21 | Permalink

Hi,

That is a really good idea.

I am trying to implement a similar visual on a bar chart. I would like to see number of open issues by processes as bars and the maximum number of open issues we can have for each process as a line on bars. So the number of issues shouldn’t be higher than this line, if exceeds I need to indicate this somehow.

Do you have any idea how I can implement this?

• 4
Petra
February 26, 2015 - 20:54 | Permalink

The trick is excellent!
But as for the visualisation itself I would flip it: I would use the horizontal bar as the average sold items and use the colum as the number actually sold per person. This way you use the horizontal like a marking line (metaphorical speaking: did the salesman jump across the average numeber of sales?). But maybe that is a matter of taste…

• 5
Mark
February 28, 2015 - 00:21 | Permalink

Very nice ! Thanks for sharing. I tried to achieve a similar goal some time ago but did not succeed. I already used your solution today. Thanks again…

• 6
Bryon
March 7, 2015 - 03:28 | Permalink

I love the idea and agree, it’s more visually appealing than using dots. Hoping to implement it. Thanks for sharing.

I am seeing that for the “Error Below” line, if the difference in the two netted values is greater than that of the bar value(e.g. (-[100-30] > -30, the graph assumes that there are values in the negative and modifies the Y access. Not sure if I’m explaining it that well, but for those who understand my gibberish, are you seeing the same?

In order to get around it, I’ve adjusted the Static Min to 0.

• 7
Stefan
March 17, 2015 - 17:59 | Permalink

Bryon, I’m having the issue when one of the values is 0.

I’m comparing Plan (as the error bar) vs. Actual (as the actual bar in the chart). When for any given month the Actual is 0 and there is a Plan value, then the chart also modifies the Y axis to show the negative total of that month’s Plan value.

Another thing I’m experiencing is that each bar in the chart is only 1/2 the width it’s supposed to be. I have a hunch that this might be because I’m invoking two dimensions for the calculation of the error bars:

`=Sum({\$} Sales) - Sum({\$} Sales)`

Can anyone confirm this?

Thanks,
-Stefan

• 8
Stefan
March 17, 2015 - 18:03 | Permalink

Right, the script filtered out my set analysis because of being to HTML-ish. Now again without the angle or pointy brackets:

`=Sum({\$PlanActual= {'Plan'}} Sales) - Sum({\$PlanActual = {'Actual'}} Sales)`

-S.

• 9
March 26, 2015 - 22:26 | Permalink

That’s a great workaround – Much easier than one I had previously implemented using three expressions stacked on top of each other.

Unfortunately, I can’t get the error bars to show up at all. ?!? The box is checked, the expressions are there, I even changed them to display “Thick” and bright red so I couldn’t possibly miss them – Nothing. Hm.

• 10
David Alcobero
April 15, 2015 - 14:26 | Permalink

Interesting. I would do that with a stacked chart, using a transparent background expression in between (where I can increase the thickness of the line, and change the color at dimension level, if needed).

Still interesting know different ways of doing same things, you never know when it’ll be useful.

Good post!

• 11
Nazzer
June 18, 2015 - 06:18 | Permalink

What happens when i select more than one dimensions… The bar charts look good but the dots appear in the middle lined up vertically. I dont want this to happen, i want each dot on every bar. How can i do that?

• 12
John
October 29, 2015 - 04:24 | Permalink

Hi,

I used this to show actual vs Budget or Target.

it looks as beautiful as yourself.

thanks for great tip.

• 13
October 31, 2015 - 10:59 | Permalink

Ok, awkward…. :\

• 14
December 3, 2015 - 14:11 | Permalink

Great Post

really needed this solution ..

• 15
Karl
January 21, 2016 - 07:34 | Permalink

Excellent post, Frédérique.

• 16
February 19, 2016 - 06:59 | Permalink

Hello Barry and Frédérique,

I´m following the steps in the post and I find the representation quite powerful to express the “hole” or the superavit in sales, thank you very much.

However, when I click “Has error bars” my bars move a little to the left, and are no longer cenetered above the label of the dimension, I have moved everything, and its kind of driving me nuts not to hae that centered, I was wondering if there was something I may ve overlooking for this final tweak?

• 17
Shushana
January 2, 2017 - 13:43 | Permalink

it’s exactly what i was looking for,
Big Like !
Thanks !

• 18
Krishna
February 3, 2017 - 08:03 | Permalink

Thank alot for such a nice explanation