Method To Highlight Dimensional Groups of Data in Tabular Charts

Posted by qvdesign on February 10, 2012 · 3 Comments 

Now that’s what I call a dry heading! It sounds more like a doctoral thesis than a blog post but it’s the best I could think of to describe what’s being shown here. So what exactly are we doing and why? A relatively common request I had from customers whilst I was a consultant on the road was a seemingly simple and innocuous ‘can we highlight groups of data in a table chart to make it easier to read’ – not the most vital thing in the world but something that can really make a big difference to the readability of a large table of data. In the image below you can see the first group ‘Cornish Pasty’ in white, the second ‘Honey Ham’ is light green and so on down the column, whilst in the second image you can see that we’ve done the same with the secondary dimension of ‘Name’ and created alternate highlighting.

It may look relatively easy...but it's not.

That all seems easy enough doesn’t it?, well to do that on it’s own isn’t as straight forward as you’d imagine; the settings under the Properties > Style tab won’t help much as they’re ‘dumb’; only highlighting every N rows and we need something dynamic.

So what’s the solution? Complex and way harder than it should be (please let me know if I’m missing something), I’ve had to use a combination of the following functions spread across several variables: even() – never had to use that before in my Qlikview career, match(), if(), argb(), chr() and concat(). I won’t go through it in fine detail as the .qvw can be downloaded via the link below but the gist is that we use a the match() expression to get the position of the dimensional value in a concatenated string of the posible values of that dimension and then if it’s even we colour the background with the argb() – I said it was complex. Due to the way concat() and match() work we need to use the variables to pass through the the text strings as using commas causes confusion; take a look to see what I mean.

To make it a little more usable I’ve added an island field to allow the user to select which dimension they want to highlight across; in this case Name or Product; the dimension can either be primary or secondary. Included in the qvw is both a Straight Table and a Pivot (via Fast Change) both of which allow you to drag and drop the columsn whilst maintainng the alternate highlighting, the Pivot also supports using the secondary dimension horizontally resulting in highlighted columns.

There are a few issues however: you can’t sort the Straight table by anything other than the dimension you’re highlighting across and selcting to display only the TopN can also casue things to go a little haywire, that said I think it’s potentially a useful tool, you aren’t going to use it on every table as long as you think about how the user is goign to use it (I’ve disabled the interactive sort to stop those pesky user messing with my objects!)

So there we have it a complex solution to a simple looking problem, not a bad way to ponder away a Friday afternoon.

Download the .qvw here: https://docs.google.com/open?id=0BxloTMUod74tMDZkYjllMTMtM2ViZS00YjkxLWFlNzctMmQ1ZWIyOTExYTVi

I’ve submitted this as an ‘Idea’ on the Community (to make it simpler and more reliable to implement of course) you can vote for it by following this link: http://community.qlikview.com/ideas/2292

UPDATE: I’ve added the Data file here: https://docs.google.com/open?id=0BxloTMUod74tMTVmOGUzYjgtMzFmNS00MWU2LWJjZTktMDM4MDU4OTlkY2U3 so people can download the Sales Data used in the .qvw andsee the effect with Personal Edition.

All the best,

Matt

Filed under Visuals