Target lines on a bar chart using error bars

By Frédérique Verhagen

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:

Error bars ComboChart

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:

Individual target lines on a QlikView bar chartIn 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):

Using a combo chart

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:

Elements in Error bars

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)

Error bars expression tab

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

Error bars presentation

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

Error bars Chris

Error bars Collin

About The Author

Frédérique Verhagen

Hi! I am Frédérique, I graduated in Business Mathematics from the Hague University of Applied Sciences and am working as a Junior Business Intelligence Consultant at Bitmetric since February 2015. Via my blog posts, I will share interesting QlikView/Qlik Sense features and applications that I come across. My main subject areas will be data visualization and applied mathematics.

19 Comments

  • 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.

  • 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?

    any hints! please anybody! LoL

  • 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

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Karate Kid
    What QlikView developers can learn from The Karate Kid

    As you might have guessed from some of my earlier posts, I am a sucker for popular culture from the...

    Close