Creating a bar chart in a straight table

Straight tables are great for displaying numbers. Bar charts are great for showing the information visually. A great thing that you can do in QlikView is combine both—using linear gauges.

Getting ready

Load the following script:

LOAD * INLINE [
    Country, Total Debt, 0-60, 60-180, 180+
    USA, 152, 123, 23, 6
    Canada, 250, 100, 100, 50
    UK, 170, 170, 0, 0
    Germany, 190, 0, 0, 190
    Japan, 90, 15, 25, 50
    France, 225, 77, 75, 73
];

How to do it…

Use these steps to create a straight table containing a bar chart:

1. Create a new straight table. Set the dimension to be Country. Add two expressions:

   Sum([Total Debt])
   Sum([Total Debt]) / 
   Max(Total Aggr(Sum([Total Debt]), Country))

2. Set the Total Mode property of the second expression to No Totals.

   

3. Change the Representation property of the second expression to Linear Gauge.

4. Click on the Gauge Settings button and enter the following settings for the gauge:

   Guage Settings, Max	1
   Indicator, Mode	Fill to Value
   Indicator, Style	Arrow
   Show Scale	Off
   Autowidth Segments	On
   Hide Segment Boundaries	On
   Hide Gauge Outlines	On

5. There should be two segments already there. Remove Segment 2, leaving only 1.

6. Set the color of the segment to an appropriate color. Pastels work well here.

7. Click on O K. Click on Finish to close the chart wizard.

   

How it works…

The AGGR expression returns the maximum value across all the countries. In this example, 250 from Canada. If we then divide the total debt for each country by this maximum value, we will get a ratio with a maximum value of 1. This is exactly what we need to create the bar chart with the linear gauge.

There's more…

This technique can be utilized anywhere that you need to create a bar chart in a table such as this. The added visual can really bring the numbers to light.

See also

• The Creating a modified bullet chart in a straight table recipe

• The Creating a Redmond Aged Debt Profile chart recipe