Qlikview Contiguity Chart (aka ‘That Chart From the Windows Disk Defragmenter’)

If you’re thinking: “Wow! – The Windows Defragmenter Chart in Qlikview…nigh on useless.” then don’t be too hasty; there’s far more value here than first meets the eye, this can potentially be a very useful chart as I’ll hopefully show.

Of course we aren’t using this chart in the same way as Windows does, in-fact it’s really just a coincidence that the two look the same, actually come to think of it what does the Windows chart actually show? – the majority of people will look at it an hit the ‘Defrag’ button simply on the basis whether it’s ‘red and stripy’ or not (I know I did). In my version we’re looking at Products and whether or not they’ve been sold during a day or not – simple as that, it could be far more complex; has a target been reached, change the colour based on top selling Sales Person, alter the hue based on daily sales etc etc.

That’s all well and good but what can it show us? Take a look at the finished example below. The chart is telling us several things; ‘Smoked Salmon’ had a period of sustained day after day sales in mid-2009, generally speaking sales are getting more frequent, ‘Scotch Eggs’ are bought more frequently than other Products and that’s all without looking too hard, knowing the data (it’s random development data) and making no selections in the dashboard. Of course instead of simply looking at daily ‘Yes/No’ Product sales we could be looking to see whether Sales People have met a particular target each day (or each week for that matter) or if we’ve sold out of a Product each day, this possibilities are wide and varied.

Here’s how it’s created (there’s a caveat so be careful):

Firstly; as always the .qvw can downloaded here: https://docs.google.com/open?id=0B0nQ9tZcY4wzV0FHOHJqQXVUeUd5NVVYMmFWYmtDdw

I should also point out that this is purely a Proof of Concept; the example here is provided to show the principal and how to achieve the basics, I’ll leave it to individuals to integrate with particular datasets.

To begin with I’m using my standard development dataset; Sales, Sale Date, Volume, Sales Person, Product etc – about as simple as it gets, I’ll upload the .xls as well so people with Personal Edition only can still reload the app.

Over and above the standard simple load of the data from the .xls we need to do a little bit of prep work to get the data ready for the chart.

As with most sales type datasets we don’t have a sale for every Product (our X-Axis) on every single day (effectively our Y-Axis) which means we can’t generate the chart as we only have data for the ‘Yes’ days and nothing for the ‘No’ days. Therefore we need to create an additional table with the appropriate data (I’ve created an island for simplicity) by using the following script:

Without going into all the detail; we’re creating a new table with all the possible Sales Dates (in the same way as the standard Qlikview calendar is created) then we use a Cartesian Join to link the daily Product Sales. This Cartesian Join is where the caveat comes in; if you have many years’ worth of Sales Dates and thousands of Products then the resultant table can be large; perhaps better to roll-up to say a Product Group for example.

Once created; we can begin to build the chart. First of all we’re simply using a standard Bar Chart, the dimensions are ‘Product’ first and ‘Date’ second (both from our new data island). As for the expression; that’s simple ‘=1’ as we want an equally sized line per day. The magic happens in the ‘Background Color’ expression where we need to say ‘leave blank (in this case white) if there were no sales and colour it (green here) if there were’ and we do this using the following expression:

=if(sum(Island_Sales)=0,rgb(255,255,255),rgb(101,173,33))

We need to make a few further adjustments to get the chart working; I prefer it to be horizontal so we need to alter the alignment via Properties > Style > Orientation and we also need to make the Bars stacked to create the one day after another effect. Under the Dimension Properties I personally I like to take the Dimension labels off in these type of charts and we shouldn’t have ‘Suppress Where Value is Null’ or ‘Show All Values’ checked. In my example I’ve set the Properties > Presentation > Bar Settings to be 0 for Bar Distance and 1 for Cluster Distance although this is very much personal preference. To get the axis to display correctly I’ve added a ‘Static Max’ with the following expression:

=count(distinct Island_Dates)

The glaring problem: the lack of a date axis. As we’re stacking ‘=1’ on top of each other we can’t automatically show a Date as the Y-Axis which impedes the quality and readability of the chart therefore we need to create our own after hiding the useless original. I’ve done this by adding 3x Properties > Presentation > Text in Chart items. The first has ‘date(min(Island_Date))’, the second ‘date(max(Island_Date))’ and the third ‘date(median(Island_Date))’ – after all these years I’ve finally used ‘Median()’! The items are simply placed accordingly: Min to the left, Max to the right and Median in the centre and surprisingly from my tests it seems to be accurate even post-selections and re-sizing of the chart. You could add more by adjusting the calculation eg: ‘date(min(Island_Date)+(median(Island_Date)/2)’ to generate an item for ¼ of the way along the axis.

There we have it the ‘Contiguity Chart’. As mentioned at the start this chart is flexible enough to be adapted to many situations as well as making the chart itself show more; I’ve created examples that highlight certain seasonal date periods, have stronger hues depending on the sales value and show a different colour depending on which Sales Team secured the most sales of that particular Product on that particular day.

We could show this information via the Activity Ribbon Chart (https://qvdesign.wordpress.com/2012/01/18/activity-ribbon-chart/) but even with its compressed style we’d struggle to fit a large time period into a realistic footprint whilst with a Contiguity Chart we can as here show 3yrs worth of daily information in a small efficient space; the example image above contains over 7,500 data-points

You can download the basic development dataset here: https://docs.google.com/open?id=0B0nQ9tZcY4wzeDk4ejE1R01UVHloa0FiRGVrclUyQQ

As always; I hope you can put it to use.

All the best,

Matt

Advertisements
Comments
2 Responses to “Qlikview Contiguity Chart (aka ‘That Chart From the Windows Disk Defragmenter’)”
  1. Alexander says:

    As always you keep delivering awesome stuff 🙂

    This would be very interesting to plot together with sales rep meetings with new clients and correlation with pipeline generation.

    Early warning signs where larger caps would indicate a drop in pipeline a few months ahead depending on sales cycle length.

    Off-topic: I have never been a fan of chart icons, XL and print, myself and tend to use the drop down menu instead. Thoughts and reasoning behind them from your point of view?

    • qvdesign says:

      Alexander,

      I’m glad you’re liking the blog.

      I largely agree with you about the usage of icons in chart headers. Ever since my early QV days I’ve always removed the Maximize icon by default and only used the Minimize where Auto Minimize is in place, as a rule I never add additional icons. I on the whole leave the Print and Excel icons in as these are the ones I find are used most by users – that said I should probably start removing them as well because in many cases (especially a chart like this) they aren’t always relevant (sending the Contiguity Chart to Excel will be meaningless so I should have removed it).

      I guess it’s like chart selection; there’s a time and a place.

      I think the ‘Print’ functionality would get more use if you had the simple option to print the chart actual size and not have it blow up to fit the page (if there is a setting to do that then I can’t find it).

      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: