Key Performance Indicator Approaches (Redmond Pie-Gauge)

The "traditional" approach to presenting Key Performance Indicator (KPI) values on a dashboard has been to use circular gauge.  Something like this:

Stephen Few has proposed the Bullet Graph as a much more effective visualization, and I tend to agree with him, but there is just something about circular objects that appeals to people.  Even though we tell them that Pie Charts don't work as well as Bar Charts for a certain visualization, they users demand Pie Charts.  Propose a Bullet Chart and they request a gauge.  I tend to push back on these requests, but not always militantly.  I will allow them because I understand that there is an emotional element within dashboard design that needs to be catered for.

When I do push back though, I like to be able to present alternatives.  Within QlikView, we have several different options for presenting KPI type information.  For example, the data can be presented in a straight table with a linear gauge:

The gauge here is effectively acting as a modified Bullet Graph.  I discuss the creation of this in my QlikView for Developers Cookbook.

Another effective way of presenting this information is by using a horizontal bar chart to display the percentages:

This chart quickly shows the user the relative performance of each country.  The colored blocks show the extent by which a country has exceeded, or failed to reach, the target.

Building a chart like this led me to think about other ways that this could be presented and led to the design of the Redmond "Pie-Gauge" (*** can't find anything like this usage online, but let me know if you have seen it before ***):

This, I think has a couple of things going for it.  Firstly, it works like a good pie chart should - low cardinality part-to-whole comparison.  The circle represents either the total sales or the total target - whichever is higher.  The segments add up to the whole.  It also works as a circular gauge, showing the extent of good performance to the right and sub-target performance to the left.  The size of the segment shows how that plus or minus performance compares to the whole.

So, it gives users the circular objects that they have an emotional attachment to and also gives a reasonably effective visualization.

Personally, I like my new chart design, but it might not be my first recommendation as a gauge alternative.  I quite like this Bar Chart representation:

Here, we can see the percentages as sized blocks versus the actual values.  We can quickly see that although Germany exceeded target by 25%, the Japan 20% represented a much larger actual value.  Similarly, the US 5% drop versus target is actually a much larger dollar value than the UK's 7.1% drop.

I would be interested in hearing other peoples' points-of-view on this subject and what they think about the Pie-Gauge.  Would it be useful for me to follow up with a post on how to create it?

---

Stephen Redmond is author of QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Enhanced Store and Drop

A very common use case in QlikView is to load a table, do some transformations, store the table to a QVD file, then drop the table.  If you have many tables in a script, writing out the whole

Store XXX to YYYY.qvd;

and then

Drop Table XXX;

Gets a bit wearing after a while.

My solution is to use a subroutine in the QlikView script like this:

Sub StoreAndDrop(vTableName)

Trace Store and Drop $(vTableName);

// Store the table to QVD
Trace Storing [$(vTableName)] to [$(vQVDPath)\E_$(vTableName).qvd];
Store [$(vTableName)] into [$(vQVDPath)\E_$(vTableName).qvd];

// Drop it
Trace Dropping Table [$(vTableName)];
Drop Table [$(vTableName)];

End Sub

This, very simply, replaces the repetitive code and I can call it like this:

Call StoreAndDrop('Customer');

A perfect use for a subroutine!

Quite often, we might want to capture additional information about the load such as how long the load took and how many records were loaded.  All of this can be captured using the sub routine.  Like this:

LET vTotalTablesLoaded=0; 
LET vScriptStartTime=now(); 
Let vLoadStartTime=now();

Sub StoreAndDrop(vTableName)

Trace Store and Drop $(vTableName);

Let vTotalTablesLoaded=$(vTotalTablesLoaded) + 1;
Let vLoadEndTime=Now();

// if needed
UNQUALIFY *;

TablesLoaded: // replace with join(TablesLoaded) after first table
LOAD
   '$(vTableName)' as [Loaded Table Name],
   NoOfRows('$(vTableName)') as [Number of Records Loaded],
   '$(vLoadStartTime)' as [Load Start Time],
   '$(vLoadEndTime)' as [Load End Time],
   Time(TimeStamp#('$(vLoadEndTime)')-TimeStamp#('$(vLoadStartTime)')) as [Table Load Time]
AutoGenerate (1);

// if needed
QUALIFY *; 
UNQUALIFY [Join_*], [*ID];

// Store the table to QVD
Trace Storing [$(vTableName)] to [$(vQVDPath)\E_$(vTableName).qvd];
Store [$(vTableName)] into [$(vQVDPath)\E_$(vTableName).qvd];

// Drop it
Trace Dropping Table [$(vTableName)];
Drop Table [$(vTableName)];

Let vLoadStartTime=now();

End Sub

Thanks to the very tallented Carly McClintock for inspiring this post.

---

Stephen Redmond is author of QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond