Creating a distribution bar chart

In this tutorial you will learn about the creation and value of a distribution bar chart. To be able to develop this chart we advice you to download the necessary QlikView file, it can be found at: http://qlikshow.com/Qlik/Exercise app.qvw

Introduction
A distribution bar chart should show you the distribution of products / customers / etc over class buckets. This example also demonstrates how to add color coding for another measure to add more value to the chart. In this particular example we will use a distribution of customer count over the sales buckets with a classwidth of 100K.

This will show the user how many customers there are in which sales buckets. This is quite useful when you’re doing analysis in the sphere of customers segmentation.

Adding a new chart to the canvas

media_1397808775946.png

After making a copy of the sheet ‘TPL 1’, and adding a new title to this sheet, for example ‘Distribution’, you should add a new chart object to the canvas.

1. right click on the canvas, choose: New Sheet Object > Chart > Bar Chart
2. Click Next >

Adding the (calculated) dimension

media_1397808879646.png

To be able to calculate the revenue buckets for this chart we need to add a calculated dimension.

1. Click on Add ‘Calculated Dimension’

2. Add the following expression:

=class(aggr(sum(Quantity*SalesPrice),Customer),100000)

(This expression will create a range of sales buckets, calculated over the revenue per customer, with a width of 100K each)

3. Under label add: “Sales Buckets” as the name for this dimension

Adding the expression

media_1397809249126.png

Now, to count the amount of customers, do the following:

1. Go to the ‘Expressions’ tab
2. Click ‘Add’
3. Copy the following expression into the expression dialog:

count(DISTINCTCustomer)

4. Click ‘Ok’, then under label add the name ‘Customer count’ for the expression
5. Click ‘Finish’

We should have the following chart…

Configuring the chart

media_1397809589333.png

This chart isn’t finished as it is shown here. We still have to apply some (information) design to it to make it work.

The idea is of a distribution chart is:

The concept of a distribution chart

media_1397810048021.png

In this chart, each bar shows the amount of time users spent on fixations within a 100-pixel-wide stripe running down the screen, starting from the very left.

People spent more than twice as much time looking at the left side of the page as they did the right:

Left half of screen: 69% of viewing time
Right half of screen: 30% of viewing time

The remaining 1% of viewing time was spent to the right of the initially-visible 1,024 pixels. Such information is visible only after horizontal scrolling, and the minute amount of attention it attracts confirms the guideline to avoid horizontal scrolling

Source: http://www.nngroup.com/articles/horizontal-attention-leans-left/

Adding information design to the chart for comprehension

We want our users to get the best possible experience when it comes to analyzing data with visualizations we create for them. For our distribution chart we can do the following:

1. Remove the caption
2. Remove the borders
3. Set the font-size for the axes to 8 pts
4. Configure for diagonal labels on the x-axis
5. Setting the title of the chart to “Distribution of customer over sales buckets
6. Left aligning the title, change the font-size to 10 pt and give it the color that comes with the variable ‘cGreen’
7. Configuring the sort order for the x-axis (Sales Buckets) to ascending order on ‘Numerical Value’
8. Adding the value label for the expression to the bars
9. Setting the bar distance to 1
10. Setting the cluster distance to 1

Adding color coding for net margin

media_1397812154392.png

To help our users to determine whether there is a relation between customers and their accumulated revenue versus the net margin % we could add color coding to the bars.

To achieve the result as seen above we should use the colormix wizard in combination with the expression for net margin %.

1. Go to the ‘Expressions’ tab, click the ‘+’ sign next to your ‘Customer count’ expression
2. Open the Background color expression dialog
3. Under the menu in ‘File’ you will find a menu-item called ‘Colormix Wizard…’, Click on it
4. Skip the first wizard screen by clicking ‘Next >’
5. Step 1: Add the expression for net margin % to the ‘Value Expression’ dialog, use this expression:

(sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity)

Defining the colors for color coding

media_1397813263659.png

6. Step 2: Definining your colors:

a. Your color for upper limit should be the variable ‘cGood’
b. Your color for intermediate limit should be the variable ‘cMedium’ and the target reference should be ‘0’
c. Your color for lower limit should be the variable ‘cBad’

Value saturation setup

media_1397813369363.png

The last step of the wizard is about value saturation. You shouldn’t have to alter these settings, click ‘Finish’. QlikView will generate the following expression to calculate these colors for you:

ColorMix2 (if((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity) <0,-Sqrt(-((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity) -0)/(0-RangeMin (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity) ,1,NoOfRows(total))))),Sqrt(((sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity) -0)/(RangeMax (top(total (sum(SalesPrice*Quantity)-sum((CostPrice)*Quantity)-
sum((SalesPrice*Quantity)*FixedCostFactor))/sum(SalesPrice*Quantity) ,1,NoOfRows(total)))-0))), ARGB(255, 230, 51, 26), ARGB(255, 128, 211, 72), ARGB(255, 249, 231, 6))

End results

media_1397813540843.png

After these settings your chart should look like this.

Bonus step

media_1397814034106.png

How cool would it be if your users could set their own class width for the distribution chart?

You could use a variable to set the value for class width in the calculated dimension.

Try it to give your users even a better experience!