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’:
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).
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:
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:
count(distinct if(QuotedDateTime>=date(v_Period_B_Start) and QuotedDateTime=date(v_Period_A_Start) and QuotedDateTime<=date(v_Period_A_End),QuoteNumber))
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,