Thanks for visiting the blog – if you are visiting here as a result of my Tableau European Customer Conference session, welcome along. The best way to keep up to date with new posts is via my twitter feed (@acotgreave).
My session was all about tooltips, and ways you can use them to enhance your visualisation and make your audience’s lives a lot easier. I have posts on various tooltip topics:
If tooltips aren’t your thing, then have a browse of the blog for other Tableau tips and tricks.
At the time of writing, the conference workbook I used isn’t yet available for download. Follow me on twitter where I will update everyone when it is put onto the Tableau Conference website. There will also be a video recording of the session should you wish to revisit some of the specific content.
I couldn’t resist adding one more post in my Joy of Six series. Even though v6 has been with us a while , I’m still discovering how to use its great new features. In particular, calculated fields are a huge new addition that require a certain amount of head scratching before it all becomes clear. This post describes how I solved a problem with calculated fields.
In my day job at the University of Oxford, we’re about to publish our annual student statistics. These have line charts of changing number of students over time, broken down by gender, programme type, nationality, etc. The dashboard doesn’t have a lot of space on it, so it’s difficult to add labels to the lines. This means that while the trends are clear, the actual values are hidden. Our users are wary of charts without labels: they are more comfortable being able to easily see the numbers.
I wanted to stick with my clean, label-free charts, and put all the information in the tooltip instead. Regular readers know I love to muck about with tooltips (whether it’s conditional formatting or putting bar charts in them). The solution that is shown here was a collaboration with Joe Mako and Richard Leeke – the two people who know more about calculated fields than anyone else I know. I’ve recreated the solution using the Superstore samples data below. Hover your mouse over a mark to see all Order values that are on the line you’re over:
The viz shows a basic small-multiple of number of orders by Region and Category. Nothing special here, and the default tooltip isn’t spectacular, either:
The key point is that it only shows the value for the mark your mouse is over. I want all the values on the line. Calculated fields allow us to do this. This post can’t cover the full scope of Table Calculations: one could write a whole book on the subject. I’ll just cover how this problem was solved.
Let’s first see how the tooltip looks in the Edit Tooltip box:
There are two table calculations in there (the AGG(…) fields), and they are both on the Level of Detail shelf:
We build our tooltip around the mark your mouse is over. The highlighted mark is the one that appears in bold, red. It’s no different than the default tooltip. The magic is in the calculations. Before we look at the calculation, I’ll summarise what I want to show in the tooltip. Depending on which mark I have selected (is it first in the line, in the middle, or at the end?) I want some values before or after the current mark:
We can’t avoid it any more, we need to see how the Text Before and Text After fields are written. It’s not pretty. In fact, it’s too wide to even fit in the width of the blog. Here’s a thumbnail of the Text Before field. Click on it to see its full “beauty”:
What on earth is going on in there? Here’s the logic:
Let’s say your mouse is over 2009. The calculation iterates through each year in the dataset (using a parameter called Max Year In Dataset). For each year that is less than 2009, it builds up a string with a carriage return, the year, a tab character, and the sales from that year, using LOOKUP, offsetting from 2009. Yes, it’s convoluted. You may even think it’s not worth the effort. But, if you’ve made it this far in the post, I’m thinking that you’re with me! The Text After field does the same thing for years after the year your mouse is over.
Once you’ve written the field, you need to think about the addressing and partitioning. I can’t go into a full description of the principles here (but the online help was good enough for me to get my head round the basics). For this viz, we are only interested in the Year of Order Date field, so, once the calculation is dropped onto the Level of Detail, set it to Compute Using > Year of Order Date:
You’ve now got all your parts in place. You need to build the tooltip and format it as you want. Phew!
Granted, that was not easy. And, given the complexity, was the juice worth the squeeze? If you had lots of space on your dashboard, a better way to do the same thing would be to have a separate sheet on the dashboard that updates on a Hover Action. If you hover your mouse over a mark, the extra worksheet is updated to show the list of orders for that pane. That would be more visible than the tooltip, and easier to implement. However, it depends on you having plenty of spare space on your dashboard.
There are several caveats of course. Here’s two. I’ve used a parameter to hardcode the maximum year value, which is a maintenance headache as the dataset changes. I’ve also limited it to show values 4yrs either side of the current year. Writing a table calculation to show anything more than 5yrs before/after the current year is going to be impractical.
As I am also discovering, there are many ways to create the same solution using table calculations. It’s also very easy to create table calcs that slow your workbook down. The solution outlined here is possibly the fastest. I certainly came up with much slower ways while developing this solution. For information on optimisation, I’d really recommend you check out the relevant threads on the forum. Good luck!
[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 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:
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!
This is the second post about tooltips. In this post, I will describe a technique to add conditional formatting, of a sort, to your viz. This isn’t a natively supported feature in Tableau (although it would be lovely if it was!), so you have to do a bit of extra work to do it.
I’ve implemented colour-based conditional formatting in a few Tableau Public dashboards. My disc golf analysis dashboards use colour to emphasise the good/bad performance of players. Click here to go to the viz; the tooltips are below.
If the player played better than their rating, they’d get this tooltip:
If they played worse, they’d see this tooltip:
I’ve also used colour to represent a political party’s colour, as shown in my UK General Election viz. The main dashboard is here, but I’ve embedded one of the worksheets below. Hover over the different parties (Labour, Conservative, etc) to see the different colours in the tooltips:
There are a few limitations to using colour. Essentially, this boils down to having a Dimension or Group where the possible values are known ahead of time and are relatively small. Why’s this? Because to take advantage of conditional tooltips, you need to create a specific calculated field for each Dimension value.
Here’s the steps to create the coloured Party tooltip in the General Election dashboard.
Determine the list of items that will need their own colour. For our general election, there are four. One for each of the three main parties: Labour, Conservative and Liberal Democrats, and one for everyone else (Other)
We need one calculated field for each of the four values. It is a basic IIF function that either returns the Dimension value as a string, or an empty string (note – it must be an empty string, not Null).
In my dataset, the underlying party column was a single letter, so the calculated field for Conservatives was this:
Duplicate that calculated field for each Dimension value, until you have all the ones you need:
Now put all four of those fields onto the Level of Detail shelf:
The final step is to edit the tooltip. This is quite straightforward, but looks a bit messy. Order the calculated fields so that they are all adjacent in the tooltip text. Format each calculated field with the colour that you want. For the election tooltip, this looks like:
That’s it! All but one of the calculated fields will be Null at any one time (since you can’t hover your mouse over more than one mark at a time). Therefore, despite there being four items in the tooltip, the viewer only ever sees one. And it just happens to be in the correct colour. Bingo!
Tooltips are the missing link between a mark and its underlying data. They provide a valuable opportunity to explain more about the chart without requiring the user to examine the underlying data.
The motivation for these posts was seeing so many vizzes on great charts on Tableau Public being let down by the publisher leaving the tooltips in their default state. It’s easy to think this okay, but the tooltip is an eager beaver: even viewers with hyperactive mouse movements are guaranteed to hover long enough for the tooltip to appear. Given that’s the case, you want to make sure you’re showing the user something pretty!
I’ll start off with basic good practise tips; later posts will look at advanced techniques to make your tooltips shine.
1 Make a header
My first rule when publishing a viz, is to put a “Header” into the Tooltip. The header should be the mark’s primary dimension, or a summary. For example, consider the default tooltip on the Sales Outliers sheet in the Wow Workbook:
I can’t deny that the tooltip has all the info and, yes, the tooltip is an elaboration of the mark. But it doesn’t really draw the eye, or encourage fast interpretation. Now let’s see what the Tableau staff actually did to the tooltip when they released the workbook:
See what they did? They moved the two main dimensions, and , into a header, and gave it a bolder style. Here’s how it’s done in the Tooltip editor:
2. Less is more
When building your viz, some of the dimensions/measures you add to the worksheet might be necessary for the viz itself, but have no meaning for the viewer. For example, here’s the default tooltip from my visualisation of Pub Closures in the UK:
As you can see, the are two dates, yr and Year. One is a year, and the other is a date. The latter is necessary in the viz to use a continuous scale on the x-axis, but meaningless for the viewer; all they care about is the year. Delete the unnecessary data from the tooltip.
3. Make sure the labels are meaningful
If you’re using aggregations or table calculations in your view, the default label might be something crazy like “% of Total Count of Rate:” This isn’t always too intuitive. Look again at the default tooltip from the Pub Closures viz (above).
“Difference in Min. year_number”? What? Well, it’s a nice description of the table calculation, but it’s a terrible description of the information. In this viz, it really represents “Years since party came into power”. By making all the labels meaningful, you can end up with this:
4. Order! Order!
Make sure the most important information is at the top of the list, if that’s relevant. Continuing the work on our pub closures tooltip, we should improve the tooltip like this:
5. A sentence can say more than a label
If the tooltip has quite a lot of information in it, it can be better to turn the fields into a complete sentence that fully explains the mark. A long list of labels, colons and values can be confusing. Here’s how the final tooltip for the Pub Closures viz looked:
That’s it for this first post. Next time, we’ll look at a couple of advanced tooltip tips.
