The joy of six: user-built views

Today is the Joy of Six day. Tableau v6 is incredible, and I’ve been lucky to have had my hands on the beta versions for a while. The Tableau site itself is a great resource for learning about the amazing new features and how to use them. I wanted to do a series of posts about how these new features are already being practically applied to solve our own real world problems.

A common problem we see is that we build a view for a user, only to have them say, “Hey, that’s great, but instead of Profit, can you show Sales? Oh, and also, I’d like to see it broken down by Container, not State”. In the old days (pre v6) you had to build a view for each request. Not now. Oh no. Not now. Now you can build in masses more flexibility into your views.

The dashboard below shows an example using Tableau’s sample data:

How do we do this trick? First let’s look at the pills that build this view.

Instead of using the actual dimensions or measures, each pill is either a parameter, eg “Choose columns”, or a calculated field based on a parameter, eg “Chosen column dim”.

There are four parameters in this view (for more on Parameters, check out this video on Tableau’s own site):

Each of these is a String List, looking something like this:

Each item in the List of values box should be a Dimension (in the above image) or a Measure (not shown) you want to the user to be able to choose. You need a parameter for each user-editable area of the worksheet.  In my example, that’s a parameter for Row, Colum, Bar length, and Colour.

Once you have your parameters, show them all on the worksheet (right-click on the Parameter name and choose Show parameter control).

Our next step is to connect some calculated fields to each parameter. The “Bar” calculated field looks like this:


The IF statement returns a different Measure, depending on the value of the Parameter.  There are four calculated fields – one for each parameter. I created the following for the viz above:

  • Chosen row dim
  • Chosen column dim
  • Bar
  • Colour

Now you can build your view – drop the calculated fields onto the relevant shelf, and you should now have a dynamic view where the user gets to choose what they want to see.

The final thing you should do is format the tooltips, as the default ones will make no sense. See my previous posts on tooltips for some tips.

Download the workbook to explore how it’s done in more detail.

Happy birthday #FridayMix

Those of you who follow me on twitter (@acotgreave) will probably know that most Fridays I get involved in the rathe excellent FridayMix. This week it reaches its first birthday. I downloaded the full list of all 3500+ tracks that have appeared on a FridayMix and produced this viz of the most popular artists.

London 2012 ticket prices

London 2012 published the Olympic ticket prices today. There’s lots of good value seats to be had. I took the data from the London 2012 site and built this viz that you can use to find out how much your favourite sport will cost you:

Note: this viz does not include the Opening or Closing ceremonies. Top price seats for those will set you back a max of £2,012 and £1,500 respectively! Ouch

Visualising golf tournament results

Warning! This post discusses disc golf. If you’ve not heard of it, just imagine I’m referring to “normal” golf.

The US Disc Golf Championships, the world’s toughest, and richest event is happening this week. The leaderboard’s on the PDGA.com, and on the USDGC sites are ok, but don’t reveal very much.

Here’s my leaderboards, made using their data. The first shows the ranks of the players, showing you how many birdies and bogies they got. You can click on a player, or multiple players, to see how they did in each round (this viz will be updated as round scores are available; you may only see Round 1 scores for a day or so):

The second viz shows how we can use the data to analyse things other than players. Because we know how everybody scored on every hole, we can adapt the viz to analyse each hole, and see which played the hardest or easiest:

Both of these dashboards can be seen in full screen. Click here for the player dashboard, and here for the hole-by-hole dashboard.

Telecoms companies: who’s suing who?

There’s a chart doing the rounds that shows who is suing whom in the telecoms industries (eg at the Guardian). David McCandelish has improved it nicely here, and asked the question of whether companies who’s revenue is going down are more litigious than those on the rise. Well, I took his data from here and created the charts below.

There are lots of caveats: I excluded Smartphone Technologiess LLC because the data didn’t say if their revenue was up or down. That’s a shame, because they’ve got the most lawsuits open. Also, we don’t know if the revenue was going up or down when the lawsuit was started.

PS – for Tableau geeks, the tooltips on the chart are using my Conditional Formatting trick (click here) to make the text colour change depending upon Revenue Direction.

Charts inside a tooltip? Yes, we can.

A classic from 2010!

[Update: in theory, v6.1.1 has made it easier to create the bars described in this post by using REPLACE() and SPACE(). However, at time of writing (19 Aug 2011) there is a problem: while the solution works fine in Tableau Desktop, the view won’t display on Tableau Public – have a look at the “Using REPLACE and SPACE() tab in the embedded workbook below. The calculations have also been optimised using parameters.]

Consider this map:

It highlights one of many situations when pie charts don’t work. The smallest pies are so small you have no chance of making out the size of the slices.  And what if I wanted to show Profits on the viz, as well as Sales and Product Type?

The Tableau guys have come up with a way of using Gantt Bars to emulate a bar chart and maybe solve some of these problems. This works okay, but has its own flaws; mainly that it’s still hard to make out the individual bar segments.

Well, how about using a bar chart inside a tooltip? Surely you can’t do that? Well, yes you can:

 

The viz above solves several problems. The circle size is based on Sales, and Profit by colour. And the tooltip shows the proportion of sales of each Product Type. With just a mouse-hover, you’ve revealed a new dimension on the data. Three dimensions on one map viz – bingo!

How do we do this? Well, first we need to decide if our dataset is suitable. First of all, you need a known number of values in your tooltip-chart dimension, because we’ll be creating a calculated field each one. In the above, we only have four Product Types, so that’s a suitable candidate. Secondly, there is a maximum length that the bar can be. Too long and it will wrap around in the tooltip. As we’ll see, the bar is built using a text string, so we can’t just have an unlimited bar with a value in the 100s. Percentages work best as they are going to have a maximum value of 100.

For each Product Type, we need two calculated fields. One works out the percentage of sales of that product type:

Coffee Sales Percentage

and the other works out the length of the bar. How do you build a bar out of text? Simple: use the ALT+219 ASCII character. That’s this one: █. You create it by pressing the ALT key and then 219 on the Number Pad (using a laptop? Copy and paste the one in this blog instead!). We need to create a parameter – [Bar] – which is one loooong string of 100 █ characters, and then create a calculated field that is a subset of that, based upon the % value in your other calculated field. Here’s the text of that field:

LEFT([Bar],ROUND([% coffee sales]))

Now we have two calculated fields for each Product Type. Drop them all onto the ever-so-useful Level of Detail shelf, and now you can build your tooltip as follows:

You’ve done it! You’ve now become a tooltip genius. Using the skills we’ve learned in our other tooltip posts (basics here, and conditional formatting here), your tooltips can be as jazzy as your vizzes themselves!

Those of you who are big Excel users may know where the inspiration comes from. This post gives a huge Tip o’ The Hat to the posts from Juice Analytics, Daily Dose of Excel, Jon Peltier, and Chandoo that showed us all how to do in-cell charts in Excel in the last few years. Also, thanks to Richard Leeke on the Tableau forum for giving me the last piece in the jigsaw for this idea: Excel has a REPT function that can be used to create the long string. Tableau doesn’t. Richard pointed out that the only way round this is to type the string out manually and take a substring of it. Nice!