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 Fontstruct.com (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 (www.datadrivenconsulting.com) 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)

2 thoughts on “Sparkbars in tooltips”

  1. Andy, this is great stuff.

    Here is another approach to your formula for creating the spark values:

    PREVIOUS_VALUE(“”)+STR(ZN(ROUND((SUM([Sales])/WINDOW_MAX(SUM([Sales])))*9)))

    This will do what all your lookup functions do, concatenating a spark value for each month. The issue you describe of missing months in the data can be resolved with worksheet layout, partitioning and a helper calcs for filtering the display and/or repeating this calculated value for every mark.

  2. Andy, great article !!

    If we have corporate guidelines against installing custom fonts, is there a way to replicate the bars using an existing Windows font ?

    Regards,
    Ujval

Leave a Reply

Your email address will not be published. Required fields are marked *