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!
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.
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.
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
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.