The Parallel Ranking Use Case

For this use case I’ll use statistical data from the EU regarding airline operations in 2012. For each country, I have several measures available :

  • Number of flights departing from a given country.
  • Number of seats available on flights departing from a given country.
  • Number of passengers boarded on flights departing from a given country.

To simplify this example, we’ll use only the 10 countries with a higher number of departures in 2010. The challenge here is to create a top 10 ranking using this countries and to be able to compare how a country who is the number one in a given measure (let’s say number of flights) is ranked along the number of boarded passengers. This is the final result :


A simple conclusion from this chart : France is the third country with most flights in 2012. But when we look at the number of seats available and the number of passengers, it ranks the fourth place. Spain has the opposite behavior. One explanation for this may be the size of the aircrafts departing from one country and the other, but for now we’re not going to focus on that “why” analysis.

To achieve the above results, you’ll need to follow this steps :

  • Start by creating a bar chart for each measure. Since we’re creating a top approach, order each bar by descending Y-Value. Each chart should have distinct expression (in this example, something like Sum(Flights), Sum(Seats) and Sum(Passengers)), but it should share the same dimension (Country in our example).
  • When you click in one of the bars, the default behavior is to filter the existing data to show only that country. That is not what we need, so we need to change our expression in all the charts to something like this :

  • This set analysis will make the chart not responsive to the country selection. When you click on UK, UK will be filtered, but the chart will not respond to show only that country. This is ok, but we still need to know what country is selected, and to see that selection on every chart. For that, we’ll need to tweak the background color with this expression :

  • It’s easier to explain that this works, why it works is more complicated. My understanding is that the Country=Country expression will be evaluated looking at the first Country as the dimension on the chart and the second Country as the document field value. Therefore, when the dimension value is the one that is selected, the bar will be painted red.

Hope you’ve enjoyed this one.

Organize Macro

QlikView is a great tool for a lot of tasks, but in my opinion it surplus the competition on the initial exploration of a new data source. In the past I used Excel for this task, with the purpose of :

  • Trying to understand every column in a dataset, looking at the column name, but also at the contained data. Even when documentation is available (and most of the times it’s nowhere to be found), looking at the data will give a more clear understanding of what is available.
  • Learning how complete is your data. So you have an “Customer Address” column, good for you. Oh, 30% of the time is empty and 40% is meaningless… That wasn’t on the manual, right?

The process of analyzing a new data set is, at start, a table by table analysis. So I take a big table (from a DB, Excel or CSV file) and load it to QlikView. Now I have a huge list of fields to work with. Since I don’t know the data, they are all good at this point, so I add everything to the first sheet of the document :


The default QlikView behavior is quite messy. It adds everything up, but in a big pile of… stuff.


The next process involves a LOT of drag and drop, moving empty fields to one side, numeric to the other, dates to another corner, dividing over and over the stuff that I know from the stuff that I still need to understand.

The purpose of this post is to show you a small macro that tries to simplify this process, by organizing the list boxes on screen, and changing their color according to some basic data types (numeric, timestamp, text and undefined). After running the macro, the pile of fields will now look something like this :ArrangedFields

In the script there are three parameters that you can adjust for your needs :

  • maxPerRow : How many ListBox are distributed in each row
  • listWidth : How many pixels will the ListBox width take
  • listHeight : How many pixels will the ListBox height take

Have fun, enjoy, explore, expand!