The joy of six: calculated fields

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!

Joy of six: Gapminder, Tableau style

As soon as I got my hands on Tableau v6, the first thing that came to mind was Gapminder. Those new shiny features meant it would be possible to recreate the amazing Trendalyzer software in Tableau. Here’s how I did:

To do the viz, I took advantage of lots of the new features, many of which I have already blogged about.

Data Blending

There are 9 connections required to show the viz above:

Okay, I acknowledge that that is not a shade on Gapminder’s 250+ datasets. But, hey, I’m not Google, you know! Tableau coped well with 9 datasets. What was difficult was reshaping the Gapminder data in Excel in order to get the best out of it in Tableau. Don’t know about Reshaping? Then you need the Tableau Reshaper add-in

Parameters

There are lots of parameters in this viz. Mostly, I used them to enable the user to choose which Dimensions/Measures to display on the viz. You can read about how to do that in my earlier Joy of 6: user-built views post.

Page controls

At last, the Page Filter grew up. A valuable but neglected feature in previous versions, it’s been beefed up enormously this time around. The viz takes full advantage of Page filters and the ability to Show History. Click on a mark in the viz to see how it’s changed.

The single, pretty major issue is that on a Server or Public view, you don’t get a Play button. Given the architecture of the Tableau Server, this makes perfect sense: it is simply not possible to get data from the server to the client quick enough to work. You need to download the workbook to see the playback controls. This is a shame, as it’s a cool feature.

Tabbed views

My favourite “easy-win” from the new features is the ability to use tabbed views to create an About Box (read the blog post about this). In this viz, it allows me to create links to the source and other places, and add context to the viz. Without the About Box, I would either have to miss those out, or use up valuable screen real estate on the viz itself.

Challenges?

This project was not without challenges…

Gapminder data interpolates

On the Gapminder site, if a data point is missing, it interpolates the value. That makes for a nice smooth animation on their website. Unfortunately, Tableau cannot invent data in that way. See those lines through the circles in the image above? That’s where Gapminder interpolated in order to make a smooth interpolation.

Log and linear

Gapminder automatically switches between log and lin axis scales. This is really nice, as it removes the burden from the user, and makes everything fit properly. Alas, no such feature in Tableau as yet. I wouldn’t even expect Tableau to implement this – it’s a very niche feature.

Annotations: two problems.

See the light-grey label showing Year that appears on the chart itself? In Gapminder, that label fits the whole chart. in Tableau, area annotations are at the front, so if you try to recreate labels as large as they are in Gapminder, the obscure the data points. But that wasn’t the biggest issue. Oh no. The area annotation is specific to the Dimension being selected. Therefore, I needed to add a separate annotation for every single X-Y axis combination. That was pretty tedious…

Some Gapminder data is just plain wierd

I had to pick and choose the data I used for this viz. In some cases there were too many missing data points. And in others, the values just don’t seem credible. For example, if you choose the CO2 emissions Dimension, well, I just don’t believe those values.

Conclusions

This has been a pretty challenging viz to get right. Having a defined end-goal (Gapminder) to try and hit isn’t the normal way of developing in Tableau. It’s better to explore the data, find the story, and tidy things up when you get to wherever you’ve got to. Forcing Tableau to be like something else is trickier.

Tableau isn’t optimized as well as Gapminder. Tableau can’t match Gapminder for smoothness of animation or flexibility of showing the different data. But that’s not really what Tableau is trying to do. It does everything it needs to do in this project just fine. Most of your business applications will be simpler than this. I think the end result above is great. It was a great way to explore the new features.

I hope you enjoyed the project, too. This is the end of my Joy of 6 posts – click here to read the rest.

Joy of six: dynamic multi-member groups

First off, let me say that I do not take credit for the contents of this post. Credit is due to Joe Mako and Richard Leeke who responded to my questions on the Tableau Forum. This is a good time to say that if you aren’t using the forum yet, get over there to ask or answer all manner of Tableau questions from the trivial to the fiendish.

I am posting this solution because it is such a sweet use of calculated fields. Here’s the scenario: “I want the user to be able to see the top n customers’ average sales. They should be able to control the “n” value, and show or hide all the rest of the customers.” Here’s the answer:

I’m afraid I am not going to go through the solution. Why? Well, I barely understand it myself, and I don’t think I’d be able to explain it properly. I recommend you download the workbook and look at the forum thread to work it out. Otherwise, just be in awe at the work of Joe and Richard!

The joy of six: one against the rest

This is the third post on simple tricks that are available in version 6. This one represents a common request we get. The client says “I want to compare the performance of x against everyone else.” What is x? Well, it could be Customers, Departments, Countries, anything.

Parameters once again make this easy:

The drop-down filter is a parameter control, in this example showing every State in the Coffee Chain connection. The Tableau devs have made it easy to create a parameter containing every member of a Dimension. Right-click on the State Dimension, and choose Create Parameter…:

That’s your parameter list created – show the parameter control on your worksheet by right-clicking on the parameter and choosing Show parameter control.

Next up we create a calculated field to return either the State selected in the parameter, or all the others. This is essentially a dynamic Group, with one lone member of the Dimension, and everyone lumped together in the “Other” category. In this case, you can call the “Other” category anything you want. Here’s the calculated field:

This is the field you drop onto your worksheet. Whichever shelf it is on, it will only show two members: the State selected in the Parameter, and everyone else lumped together.

The joy of six: add an “About” box

You may have seen my response to an Information is Beautiful post. Here’s the viz I used in that post:

This uses sheet tabs to create an “About this viz” tab. It is good practise to credit data sources and date your vizualisation. The About Box allows you to do this. I’ve used parameters to store the hyperlinks, and dropped them onto worksheets embedded into the “About this viz” dashboard.

Prior to v6, the choice was either to omit the credits, or squeeze them into your dashboard somehwere. No more!

The joy of six: easy error bars

Over on the forum, Hadbar asked a question about Error Bars. He asked the question back in the days of version 5, when it wasn’t straightforward to make a pleasant error bar. However, now we have the Joy of Six, we can use the new dual axis/multiple mark features to create some visually appealing error bars:

Tableau won’t calculate the error values for you, so you need those in your dataset. With this example, you need two measures:

  • Error – the magnitude of the error
  • Error lower – the lower limit of the error bar

Here’s how we build the viz. First make a regular bar chart with the relevant Dimension and Measure, as below:

To create the error bar, we build a Gantt chart on the second axis. It’s lower limit is the value of the Error lower measure, and it’s size (height) is the size of the error. Step one is to put the MIN(Error Lower) pill on the Rows shelf, and set it to use Dual Axis (click the picture to see details):

We’ve now got a dual axis graph, but there’s some way to go yet. The Error Bars need to use a different mark type, in this case a Gantt Bar. Right-click on the axis, and choose Mark Type…Gantt Bar:

Set the Size of the Gantt Bar to the size of the error. To do this, make sure the Marks shelf is showing the correct Mark. In this case, it’s MIN(Error Lower). This is a new feature in v6. In case you’ve not seen it, you can page through all the Marks:

Once you have found the correct mark, put the Error measure onto the Size shelf – that creates the Gantt bar with the height of the error. You’ll probably now notice that you have error bars, but they’re wide, and not in the right position relative to the bar. This fat bar problem is easily fixed – just slide the Size slider on the Mark shelf to be the smallest possible. That creates a nice thin line.

How to get the error bars to align correctly? Simple – right-click on the axis and choose Synchronise Axis. While you’re there, you don’t need to show the right-hand axis at all. You can’t properly hide axes in Tableau, but you can format them so they don’t appear. Right-click on the axis and choose Edit axis. Delete the title on the General tab, and in the Tick Marks tab, set the Minor and Major Tick Marks to None. Click OK and your axis has “disappeared”.

Lo and behold – you have some error bars. Play around with widths/colours and other settings to make it appear just how you want it. Coming next in the Joy Of Six series…. using parameters to create dynamic groups.

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.