New Qlikview Proportional Waffle Charts

Firstly; I didn’t coin the term ‘Waffle Chart’ (I prefer ‘Proportional Squares’), I first came across them on the excellent data visualization blog EagerEyes (http://eagereyes.org/blog/2008/engaging-readers-with-square-pie-waffle-charts), secondly I have to admit that they are only slightly more useful as regular Pie Charts which to some of you Stephen Few acolytes will equate to nigh on useless. Personally I have to disagree to certain amount; they do have they uses and personally I feel they score highly in the engagement stakes, take a look at the EagerEyes blog post for all the background.

On to the usual refrain: ‘that can be done in Qlikview':

I did actually achieve a similar effect (and coin the term ‘Proportional Square’) about 12mths ago, the .qvw is attached to the following Community post: http://community.qlikview.com/message/131070#131070 It’s about as complicated as it gets and is controlled by 2 Text Objects being sized and positioned using macros based on the users selections. On stumbling on the EagerEyes blog I though it was time to re-visit the Waffle Chart.

So; what’s the basis of this graphical wonder; an extension, more macro controlled Text Objects, an amended Block Chart? No; it’s a Synthetic Pivot Table. Why Synthetic; simply because it has no data from the dataset driving it as you’ll see below.

1. Create the 10 x 10 grid.

Here we need to use on of my favourite expressions valueloop(). Create 2 dimensions (‘H’ & ‘V’) both with the same Calculated Dimension containing:

=valueloop(1,10,1)

Simple enough so far. For the expression simply add =1 to generate the grid; we’ll need to add a more complex function in a moment. Next pivot the ‘H’ dimension to be horizontal and ‘Always Fully Expanded’ and that should create your basic 10 x 10 grid, you’ll need to re-size the column widths to make each cell roughly square, it should end up looking like the below:

Already I think we’re in the land of awesome; we’ve created a grid using no actual data…it’s about to get even better.

2. Assign a Number to Each Cell to Relate to a Percentage

This stage is the ‘secret sauce’ of this chart; once you’ve done this stage I’m sure it’ll click as to how to proceed.

To allow us to say; ‘this cell should be green, this one should be white’ we need to name each one sequentially from 1 to 100. Try and think about it before looking at the solution; it’s not that tricky but is a bit of a mind bender.

If you’re stuck the required expression (to replace our holding ‘=1′) is:

=if(rowno()=1,ColumnNo(),if(ColumnNo()=10,(RowNo()*10),(RowNo()-1)&ColumnNo()))

This will result in the grid now looking like this:

What we’re essentially saying is; cell 1=1%, 2=2%…100=100%.

Where have the Caption and Column & Row headings gone? There’s no getting around this bit I’m afraid; it’s straight from the Cowboy’s Handbook; I’ve added 2 white Text Objects to cover them; there’s no other way that I can think of.

3. Assigning the Colour (and Hiding the Numbers)

There are various methods for spliting the whole, in this example I’ll outline the easiest and potentially most useful; colour in an area equal to the selections when compared to the whole; my example is based on Sales and is the left hand example. In the expressions Background Color expression enter the following:

=if([Cell_Number]<=floor((sum(Sales)/sum({1}Sales))*100),rgb(228,241,216)) 

To hide the numbers add the same expression to the Text Color. So now when you make selections the chart shows what percentage of all Sales those selections account for, you can of course alter the Set Analysis component to adjust the ‘whole’ element.

The right-hand example is a little more complex (look in the .qvw – the colours are assigned in a similar method) and shows how the current selections are spread across the Product Group dimension; as selections are made it changes accordingly.

4. The Final Tweak

The final stage and something that is entirely optional is an additional element that gets around the Qlikview axiom that ‘selecting nothing is the same as selecting everything'; think about that for a second it doesn’t sound to make sense but it’s what Qlikview in most cases quite rightly does. Personally I don’t think this is right for a Waffle Chart; I used to call them ‘Fill Charts'; if I select nothing then how can it show as taking up ‘100% of Selections’ in the chart; there aren’t any selections. Therefore I’ve adjusted the expression to the folowing:

=if(GetPossibleCount(Sales)=count({1}distinct Sales),white(),if([Cell_Number]<=floor((sum(Sales)/sum({1}Sales))*100),rgb(228,241,216)))

This results in the following when no selections have been made where as a normal chart would show the same as if I selected every record:

Personally I feel it makes the object a bit more of a ‘chart realist’ in how it relates to the data.

The legneds are simply created manually using Text Objects, in the case of the right hand chart you could create a simple Straight Table with the relevant field as the dimension and the appropriate colour as the expressions background.

I’ve also included a Pie and Block Chart showing the same data to compare how easily they display the information and I have to say I feel that in most areas the Waffle beats the Pie and Block for ease of use. That said I’m certainly not advocating it as being better than a bar chart but like the Pie Chart it does have it’s uses.

The .qvw can be downloaded here: https://docs.google.com/open?id=0BxloTMUod74tMThjNTkxZjctNjcyOC00OGMwLTgyNTgtNmQyZDU1MWRhNzQw

I hope you can put it to work.

Matt

About these ads
Comments
4 Responses to “New Qlikview Proportional Waffle Charts”
  1. Lewis Johnson says:

    Nice post Matt – keep up the good work!

  2. Michael says:

    Why is the expression for labelling each cell so complicated? Couldn’t you have just used the following:

    =COLUMNNO() + (10 * (ROWNO() – 1))

    Michael

    • qvdesign says:

      Michael,

      I couldn’t get what you suggest to work (v10 SR4) although it looks as though it should, a slight tweak gve me: =COLUMNNO()+(10*(ROWNO())-10) that worked fine and is much simpler than the original.

      Thanks for the suggestion and all the best,

      Matt

  3. Max says:

    Hi Matt,
    great work once again.

    I was trying to use this chart to always show the Top5 machines bought by our customers and not to show data only on selections.
    Every customer has different machines and so the machines displayed in the chart would have to change from customer to customer.
    I played a bit with rank() and aggr() but I can’t make it work. do you have any idea?

    Thanks and BR,
    Max

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 161 other followers

%d bloggers like this: