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.
Download the error bar line example
1. Load the data, the fields ‘Product’, ‘Person’ and ‘#Sold’
2. Add Variables
 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.
19 Comments
Good option.
This is an excellent trick! Thanks for sharing.
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?
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…
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…
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. ([10030] > 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.
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
Right, the script filtered out my set analysis because of being to HTMLish. Now again without the angle or pointy brackets:
=Sum({$PlanActual= {'Plan'}} Sales)  Sum({$PlanActual = {'Actual'}} Sales)
S.
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.
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!
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?
Hi,
I used this to show actual vs Budget or Target.
it looks as beautiful as yourself.
thanks for great tip.
Ok, awkward…. :\
Great Post
really needed this solution ..
Excellent post, Frédérique.
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?
any hints! please anybody! LoL
it’s exactly what i was looking for,
Big Like !
Thanks !
Thank alot for such a nice explanation