New Qlikview Indexed Explosion Quadrant Chart

So how best to visualize relative changes between time periods for your, products, sales regions, sales people, customers etc etc… a QVDesign Indexed Explosion Quadrant Chart of course! The first thing to say is that this isn’t a chart I’ve plucked out of thin air, I first saw it used by the Brussels based Think-Tank Bruegel in a rather dry 314 page report and then shown in an edition of the BBC’s Newsnight programme to show the relative economic performance of several EU Nations over time…and what better thing to think than; ‘I can do that in Qlikview’:

The Bruegel Think-Tank's Origanl Version

Why do I like this chart and how can it be of use in Qlikview?, allow me to elucidate. In my Qlikview version we’re going to add some additional functionality to really supercharge the potential here but in essence the chart shows how various ‘values’ (Countries, Products, Employees, Customer etc) performed in 2 metrics in 1 time period compared to another. For instance: we could be looking relatively at how sales and volume have changed for your customers between 2010 and 2011 or for that matter the 3rd 3 weeks of August 2002 to the final week of June 2011. The beauty of this chart is that the changes are relative; we’re showing relative indexed changes within the dimension shown.

So here’s what my Qlikview version looks like along with Calendar objects to freely select the date periods:

Now that’s whizzy! As you can see we’re covering many more dimensional values here (the calculations are quite light) and we’ve made it a little more akin to the Gartner Magic Quadrant as we’re annotating Quadrants that are ‘Good’ (Increase on both Axis) and ‘Bad’ (Descrease on both Axis), the calendar objects (and the supporting expressions) allow us to select completely free-format date periods to compare. In the example above I’m displaying the Premium and Quote Activity performance for global insurance products, as previously mentioned there are many many options for what can meaningfully be displayed using this chart. Read on to find out how I’ve created it (client confidentiality precludes me from posting the .qvw).

Method:

Firstly we’re using a Scatter chart here with 2 dimensions and 2 expresions; nothing too complex at all, create the first dimension as a Calculated Dimension with the expression:

=valuelist(‘A’,’B’)

I should say right here; I love valuelist() and valueloop(); look them up in the help file and put them to use.

The second dimension is merely the dimension you wish to show in the chart, in my example I’m using ‘Product’. Onto the expressions; as we’re creating a Scatter Chart of sorts we need to define our X positions and Y positions; in my case X is a count of the Quote Enquires and Y is a sum or Premium within each dimensional value. Therefore for our X dimension I’m using the following:

=if(Valuelist(‘A’,’B’)=’A’,100,
count(distinct if(QuotedDateTime>=date(v_Period_B_Start) and QuotedDateTime=date(v_Period_A_Start) and QuotedDateTime<=date(v_Period_A_End),QuoteNumber))
/100))

What am I doing here? The first part; ‘=if(Valuelist(‘A’,’B’)=’A’,100′ is essentailly indexing period A’s values to 100; it doesn’t matter if one Product had 10 Quote Enquires and one had 10,000; they all get a Period A value of 100 – we’re showing relative changes NOT actuals. The final section is simply calculating the percentage of Quote Enquires in Period B compared to those in Period B (we are using ‘actuals’ here in the calculation at least). Now repeat as required for the Y axis; in my case summing Premium. Now to make the expressions to work and avoid the dreaded ‘No Data To Display’ we need to create 4 new Variables (Settings > Variable Overview > Add) for the start and end points of periods A & B, these are then linked to 4 calendar objects and formatted accordingly (by setting the min and max values displayed).

Those are the basics but we now need to tweak a few of the charts settings to get it working correctly:

Centre both axis on 100 and set the step to 10; this creates the Quadrant appearance, I prefer to switch on the Gridlines, under Presentation > Representation select ‘Both Lines and Symbols’, under the ‘colors’ tab set the Transparency to around 90% (come on Qliktech; get rid of the childish sliders and give us a percentage input method – or at least show the percentage!) and finally set the expression labels to run along the axis. The rest should simply be adjusting for personal taste.

The ‘Good’ and ‘Bad’ labels are simply text objects set to be behind the chart (that’s why we set Transparency to 90%) – a bit cowboy but it works here; you could of cause use a background image).

A few notes: the chart is fully associated and re-active, from what I can ascertain even using the if() doesn’t cause too much CPU load; a Set expression could be used but as my dataset isn’t huge here and considering the fact SA hammers your RAM usage I haven’t used it here.

So there we have it; not bad for less than 2hrs from seeing the chart to deploying it. With a few simple adjustments we could easily create a more traditional Quadrant chart and even the Qliktech promo favourite; the Gartner Magic Quadrant.

Hope you can put it to good use.

All the best as always,

Matt

Advertisements
Comments
4 Responses to “New Qlikview Indexed Explosion Quadrant Chart”
  1. Just a thought: The lines emphasize the distance and direction from (0,0) to (x,y) – I think this chart is best reserved for the cases where this is more important than the (x,y) values themselves. Where it is not (like I’m guessing in the Think-Tank example), the lines are simply eye-candy obscuring the data, and a plain scatter chart would likely be a better choice.

    Cheers

    • qvdesign says:

      Kristian,

      I agree that the data shown in these charts can indeed be equally shown in a simpler Scatter Chart in much the same way that a Symbol Chart can show the same data as a Line Chart but choosing the right one for the data can emphasis and add clarity to what is shown.

      Personally I feel that whilst the lines aren’t essential they do help emphasis that we’re showing change and not simply a current position as with a traditional Scatter; you could equally argue that the lines in most Line Charts aren’t actually needed; it’s the data points that hold the meaning (The specious nature of most Dashboard Line Charts is a topic for an upcoming post).

      One thing I always try and be mindful of when creating charts is to not push the Data:Pixel ratio too far as it can often remove too much making the chart harder to initially interpret. I’d say this would be the case Bruegel chart; removing the lines still maintains 100% of the data but makes it harder to quickly infer that the chart is showing change and not actuality.

      The key thing however is of course choosing the right chart for the right situation and I certainly feel that in the main the basic Scatter Chart will indeed be the one to go for but having another Qlikview Chart in the locker as it were can (in most cases) only be a good thing.

      Matt

  2. Nate says:

    Is this chart available for download? Thanks!

    • qvdesign says:

      Nate,

      Unfortunately I created this chart for a client so it’s sat over confidential data so I can’t post and example .qvw but it’s not too complicated to work through, I usually start with a really simple tailored dataset to get a chart working and then apply it to my real data.

      Good luck and all the best,

      Matt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: