Creating a ranking chart over time

In this tutorial you will learn about the creation and value of a ranking chart over time. 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
If your users would like to see relative rankings between, for example customers or industries, over time this is your chart to go with. It displays the relative rank movement over time for any dimension you define as important. Questions like: “How does the league of my best customers evolve?” can be answered by analyzing these ranking trends over time.

This kind of chart can be done with two types of chart objects; a line chart or a scatter chart. In this tutorial we will use the line chart.

Adding a new chart to the canvas

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

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

Adding the dimensions to the chart

To give insight into these rankings over time we need to add the following dimensions to the chart:

1. Add the ‘Year’ field as a dimension
2. Add the ‘Industry’ field as a dimension

The ‘Year’ field has to be shown on the x-axis as where the ‘Industry’ field will be used to create a single line for every value in this dimension for showing the movements over these years.

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

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

rank( sum( {<Year=, Industry =>} SalesPrice*Quantity), 4 ) *(-1)

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

We should have the following chart…

Configuring the chart

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 ranking chart is:

The concept of a ranking chart

This example, we are building, for the ranking chart is inspired by the ‘Slope Graph’ (shown above) invented by Edward Tufte. There are a few points that are interesting looking at the example above:

1. The hierarchy of the countries in both 1970 and 1979 [the order of the countries]
2. The specific numbers associated with each country in each of those years [the data value next to their names]
3. How each country’s numbers changed over time [each country’s slope]
4. How each country’s rate of change compares to the other countries’ rates of change [the slopes compared with one another]
5. Any notable deviations in the general trend (notice Britain in the above example) [aberrant slopes]

This chart does this in a remarkably minimalist way. There’s absolutely zero non-data ink.

For our rankings chart point 1, 4 and 5 are important to keep in mind.

Adding information design to the chart for better comprehension

We want our users to get the best possible experience when it comes to analyzing data with visualizations we create for them. Lets solve some general design issues first:

1. Remove the caption
2. Remove the borders
3. Remove the legend
3. Hide the expression axis
5. Setting the title of the chart to “Ranking of industries over revenue
6. Left aligning the title, change the font-size to 10 pt and give it the color that comes with the variable ‘cGreen’
6. Supress all dimension values when value is null
x. Add the symbol ‘Dots’ to the line and set the size to 7 pt

Setting the number formatting for the popup label

Because we used the expression:

rank( sum( {<Year=, Industry =>} SalesPrice*Quantity), 4 ) *(-1)

In this expression we use ‘* (-1)‘ which helps us to invert the expression axis, but when hovering over a value dot in the chart we get a negative value.

We want to format this number to show up positive when hovering over a dot. This can be done with the following actions:

1. Go to the chart properties > Click on the ‘Number’ tab
2. Set your ranking expression to ‘Integer’ and adjust the formatting to:

From: #,##0 to #,##0;#,##0

This tells the object that all negative values should also be presented as positive ones.

3. Click Ok. And hover over one of the dots. It will now show a positive number for the ranking.

Adding smart behaviors when selecting an Industry

Our chart will now look like the example above. But as you can see, when we make a selection in the chart it doesn’t leave us with one line. This is because we used ‘{<Year=, Industry =>}‘ in our expression. This expression snippet is a set analysis statement in which we exclude the selections that are made on either ‘Year’ or ‘Industry’. This is quite convenient when we want to be able to benchmark all dimension values if we select on any of those two dimensions.

But….

Now our users don’t get the very good feedback from the app on their selection. We can add color coding to the chart based on our selection to indicate to the user what he is looking at, at any given time or selection.

What we want is something like:

Adding color coded feedback to the chart with persistent coloring

In this example above we see a green line and some greyed out lines. This is what we want because this example shows us a highlighted green line for the industry that we have selected.

We can achieve this by doing the following:

1. Right click on the chart en go to the ‘Colors’ tab
2. Check the checkbox which says ‘Persistent Colors’, this will achieve that every ‘Industry’ line has its own unique color in every situation
3. Go to the ‘Expressions’ tab and click the ‘+’ sign
4. Open the ‘Background Color’ expression dialog and add the following expression:

if(Industry = Industry
, ”
, rgb(247,247,247)
)

This expression will fool the chart into rendering its unique color when a certain industry is selected and greys out the rest of the lines that aren’t selected.

This is the exact behavior a user recognizes when he interacts with the chart or surrounding listboxes.

Using the best minimum value for the expression value

In the example above we see there is a lot of white space between the lines in the graph area and the legends of the x-axis. The solve this we could do:

1. Go to the ‘Axes’ tab
2. Open the expression dialog at the checkbox for ‘Static Min’ for the expression axis
3. Add the following expression:

min({1}aggr(rank( sum( {<Year=, Industry =>} SalesPrice*Quantity), 4 ) *(-1) ,Industry))

This will force the expression axis to start at the minimum value for the ranking, and thus leaves off the white space between the x-axis and the graph area.

You could also do this for the ‘Static Max’ expression, then use this expression:

max({1}aggr(rank( sum( {<Year=, Industry =>} SalesPrice*Quantity), 4 ) *(-1) ,Industry))+1

The white space on the right and left side of the graph area could also be reduced by checking the ‘Continuous’ checkbox at the dimension axis.

Add ranking labeling to the vMinYear and vMaxYear

Users sometimes like it to look at the actual outcomes of a chart. Now in most cases we don’t want to clutter data visualizations with excessive details, but to make an example, and in which it also makes a nice addition, we are adding some labels for the ranking to the chart.

But…. In this example we only want to add labeling to the first year and last year of the chart. This can be done by doing:

1. Right click on the chart, go to the ‘Expressions’ tab and click the ‘+’ sign
2. Open the expressions dialog for ‘Show Value’
3. Here we add the following expression:

Year = vMinYear or Year = vMaxYear

Now after applying these settings the chart will look like this:

End results (no current selections)

This is our chart when no selections are applied on an industry.

End results (with selections applied)

This is our chart when we apply some selections.

Any feedback or questions? You know how to use the comments 😉