Different Ways to Interact & Display Information With Bar Charts & Tables

Something of an ambiguous title for this post but it’s quite a nebulous ‘solution’ to a problem that isn’t really a problem!?

DOWNLOAD THE EXAMPLE .QVW – It will make understanding what’s been done here so much easier – https://docs.google.com/open?id=0BxloTMUod74tOHFsZC1CMlNRdnVjaHRzRkk4dlFlZw.

***Firstly; I have to hold my hands up and say that I’ve just spotted identical functionality in Tableau: http://demodepot.tableausoftware.com/views/Oil_and_Gas_1/barsource but I can say hand on heart I’ve only seen this after creating the demo. This does run into a future post topic though; “The Tableau-isation of Qlikview” where I’ll show several Tableau chart styles & types rendered in Qlikview as a) Tableau beats Qlikview hands down for out of the box aesthetics (fingers crossed for v12!) and we can learn alot from it (especially Qliktech Partners who are loosing sales to Tableau) and b) I’m fed up of people saying that ‘Tableau looks better’ – it doesn’t, it’s simply Tableau does more of the design work for you…and they have the sense to downplay 3D charts.***

A few weeks ago I created a post where we dynamically highlighted different groups of data within a Straight Table: http://qvdesign.wordpress.com/2012/02/10/method-to-highlight-dimensional-groups-of-data-in-tabular-charts/. This got me thinking about how we interact with Qlikview and how Qlikview displays information back to us, this thinking led in part to my last post about creating Proportional Fill Waffle charts (http://qvdesign.wordpress.com/2012/02/16/new-qlikview-proportional-waffle-charts/) in that they only display once a selection has been made. Traditionally in Qlikview when nothing is selected it’s the same as everything being selected and in certain cases this can be mis-leading. Further pondering along this route led me to a long held frustration that when you make a dimensional selection within a chart; let’s say a Bar Chart showing monthly sales the data quite rightly drills to that month(s) and excludes the rest…what if I want to simply highlight it and see how it compares with other dimensional values and perhaps make seasonal activity jump out? Of course the natural thing to say is; ‘the chart with no selections allows you to do that’ and yes it does but the concept below allows it to be done in a way that is much easier to interpret.

So what’s the end result here and why does it matter?

I can think of many situations where this functionality could be useful, let’s start with say a retailer who has several years worth of sales data and wants to spot seasonal cycles (as demonstrated in the example .qvw), using Qlikview in a traditional method we’d have to look for peaks and troughs in the chart which can be quite tricky and if we select periods to isolate we loose all relativity with the rest of the data. Here however all we need do is select the period we want to focus on and straight away it’s highlighted within the chart – but crucilly the other data remains allowing us to compare it for an easy answer; ‘Do we sell more in May & June each Year’ – ‘No’. Additionaly we’re also shown sub-totals for the highlighted / non-highlighted selections. The same simple solution can be put to use when presenting using Qlikview (either directly of embedded in PowerPoint); we could easily highlight say a particular Sales Team and have all members of that team be highlighted within the charts on screen making it simple and straight forward for viewers to access the information.

The example dashboard (Download lionk below) allows highlights to be made either by selecting from the ListBoxes (Highlight Selections) or by selecting in the chart itself (Hold down Ctrl to make multiple non-contiguous selections, so it’s easy to say; select the smallest months sales by clicking them in the chart, they’ll then be highlighted and selected within the wider model; but as before our perspective is maintained as the chart itself keeps the ‘excluded’ data.

The chart however isn’t completely static; make selections in fields other than those excluded by the charts expression and it will respond in the normal way. This allows us for instance to have a chart showing Sales Person performance (one bar per Sales Person), it may show that one Sales Person has more sales that the others; we click them to highlight their bar, we can now cycle through perhaps date periods or Product Groups to easily see if that No.1 sales position is maintained or is perhaps skewed by high sales in one area. Now; I know that all this information can be displayed in other ways; I’m not claiming this to be new information we’re showing it’s simply an alternate way to show it that may or may not help users interact with the dashbaord thus getting insights they otherwise may over-look.

The same technique can also be applied to a Straight Table making it easy to pick out data from within a table in much the same way; click ‘Display Tabular Version’ in the top left of the bar chart a sample:

So how does it work? The best way for you to find that out would be to download the .qvw and have a look at the expressions and background color expressions but the general gist is that we use Set Analysis to exclude the bars from selections in certain fields; namely those associated with the charts dimension, in this case ‘Week’, ‘Month’ & ‘MonthName’ so the expression is somehting like:

=sum({$}Sales)

Generating the Highlight & Fade is a little trickier as we need to use the match() function but the end result is:

=if(GetPossibleCount(Sale_MonthName)<>count({1}distinct Total Sale_MonthName),if(match(Sale_MonthName,concat(distinctSale_MonthName,’,’)),rgb(101,173,33),argb(45,101,173,33)))

I’ve added some other bits to the example file to make it easier to use; you can select ‘Clear Highlight’ and this will clear only those fields effecting which bars are highlighted whilst leaving other selections, if you hover over a highlighted bar it shows the ‘Highlighted Total’ whilst hovering over a faded one shows the faded total, there’s a switch to control whether the values are displayed on the data points as well.

I view this as a prototype, there are probably many other applications and situations where it can add value and make the data more engaging and informative; after all there’s no point displaying data in an overly efficeint manner if the user doesn’t spot the key metric or trend which is after all an importnat goal of any dashboard.

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

Download the Sales Data.xls file here: https://docs.google.com/open?id=0BxloTMUod74tVXpVd0RGQmxRWE8wdElCTk5LbEpMQQ

Hope it’s of use and interest.

All the best,

Matt

About these ads
Comments
11 Responses to “Different Ways to Interact & Display Information With Bar Charts & Tables”
  1. Samichez says:

    Impressive work Matt, I can think of a number of places I could apply this.

    Thanks!

  2. Lewis Johnson says:

    Really nice technique Matt!

  3. Lewis Johnson says:

    Matt

    The ” is missing from one of the formulas above (its correct in the QVD though), it should be

    =if(GetPossibleCount(Sale_MonthName)count({1}distinct Total Sale_MonthName),
    if(match(Sale_MonthName,concat(distinct Sale_MonthName,’,’)),
    rgb(101,173,33),
    argb(45,101,173,33)))

  4. dineshku says:

    Very Nice Example
    Thanks for Great work.

  5. QV Design – Make Qlikview beautiful (&effective) it’s true … Thanks for your post interesting stuff … you put the doubt in me i used to be a tableau maniac now i will look a little bit more in QV

  6. QV Design make QlikView Beautiful (& effective) … It’s True
    Thanks for your post & great display you put a doubt in me i will look into QV more deeply i used to be a tableau maniac

  7. Late to the party but the below equation seems to work as well and far simpler.

    =if(Sale_MonthName=Sale_MonthName,
    rgb(101,173,33),
    argb(45,101,173,33))

  8. Paula says:

    How would this work for a stacked bar chart? Let’s say I have 4 stacked bar charts, and only select one. That one should be highlighted and the other 3 should be gray it out. But also the other three should have different grey out colors.

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

Follow

Get every new post delivered to your Inbox.

Join 157 other followers

%d bloggers like this: