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.

About The Author

Bitmetric Admin

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! ;)