Commuting in the rain?


Three years ago I had a debate with someone about why they didn’t cycle to work in Oxford. “Because it’s too wet,” was their main excuse. Being a keen cyclist, I knew that it didn’t really rain that much, and even on days it rained, it was rarely at the time one commutes to work. The other person didn’t believe me: I needed evidence. I setup a Google spreadsheet and a form, and have been recording whether it rains when I commute to work every since. I now have 3 years of data, and as of today, 20 May 2011, I am in the midst of the longest ever dry spell: 82 consecutive commutes without rain. It has not rained on a commute since 28 February 2011. Below are two displays. The first shows the dry spells:

The second viz shows the summary. Headline figure: 9% of commutes are wet. That’s good odds in my book:

Many thanks to Richard Leeke of Equinox who worked out how to do the calculated fields that work out the running sum of consecutive dry commutes.

Terrific tooltip tricks: European conference session

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.

Sparkbars in tooltips

You may have read my post on the bar-chart tooltip, or seen it on the Tableau Knowledge Base. That was always something I was proud of. When Tableau released v6, and with it Table Calculations, I suspected there was a way to extend it. Once I had my Tableau European Conference tooltip session approved, I knew it was time to go the extra mile and see what table calcs could do to enhance the tooltip charts.

What could make them better? Tooltip sparkbars showing the month-by-month sales of each product type:

[Why is there no Tableau Public embedded view here? Because my solution, inspired by Chandoo’s excellent Excel blog post, uses custom fonts. These need to be installed on the client machine, so it’s unlikely it’ll work for you without them]

Download the example workbook here. You also need some custom fonts installed. These can be downloaded from (bargraph, bargraphs lifted and bargraph2)

The sparkbar tooltip is a way of breaking the detail of one mark (for example, annual sales by state) into the monthly trend. This is a free way of drilling down into the data without requiring another view on the screen simultarneously. The same technique can be used to create a single worksheet view with multiple sparkbars in it. This would be very useful if you are printing executive dashboards, for example:

Want to do this yourself? I’m going to describe my solution. I am not sure my table calculations are the optimal way of doing this, and there are some issues with this technique. If you know a way of improving the technique, please let me know.

We need a calculation the returns a value¬† for each month’s sales value, normalised so that the highest value is 9 (the highest bar) and the lowest is 0. Sounds simple, sure, but the table calculation is far from pretty:

Ouch. And you need a separate calculation for each member of your Dimension!

It’s got 12 rows, one for each month. The LOOKUP() calc finds the Sales value from the first row in the partition (January) and normalises it by comparing it to the maximum sales value for all months. The STR(ZN(ROUND… stuff tidies things up for display. The end result is a value from 0-9 for each month of the year, if there are 12 values. One big flaw of this calculation is that if some months had no sales, they are not returned as zero: they are simply not there, and your string does not have 12 characters. This could lead to misleading sparkbars. It’s also the reason that the last few rows of the calculation have the if not(isnull(… additional steps.

Once you’ve got there, all you need to do is drop the Measure onto the worksheet, and format it using the bargraph fonts. You’ll notice that they don’t appear in the font list. That’s okay – Tableau only lists the fonts it thinks are useful to you. But sometimes we know better than Tableau, so just type the name of the font instead of picking it from the drop list, and you’ll be fine.

Okay, this is great – are you free to go forth and put this in all your vizzes? Well, not quite. The tooltips will work only if the fonts are installed on the client machine. This applies to Tableau Public, Server, Desktop and Reader. Putting them into the view itself will not work on Tableau Server or Public, but it will work in Desktop and Reader, again only if the fonts are installed locally. These are pretty major caveats. However, if you’re in the business of printing dashboards for people via PDFs, this is a great way to pack a huge amount of into in a tiny space.

Is there an alternative way? Possibly. Alex Kerin ( has been enhancing his rather excellent twitter sparkbar generator to work in Tableau. This uses Unicode block characters. He’ll be blogging about this soon, so keep an eye on his blog. His method can be used on any machine, but the sparkbars aren’t as pretty as my method. There are trade-offs either way. Here’s a comparison of the methods:

Please let me know what you think – can you see any ways to do this differently or more efficiently?

Keep up with my Tableau updates via my twitter feed (@acotgreave)