Tableau Tip: Dynamic axis selections with parameters in less than five minutes

Here’s the situation: You have multiple measures that you want to view across time, but you don’t want to see all of the measures at the same time. Instead you want the user to be able to pick the measure to view.

Parameters to the rescue!

Andy Cotgreave blogged about user built views in his Data Studio days. In this blog post I will provide the step-by-step directions.  The charts types you can create with the technique are seemingly endless, but I’ll go through a simple scenario.

Step 1 – Right-click anywhere in the Measures or Dimensions windows and choose Create Parameter.  Create a parameter named “Choose a measure” with the settings below.  Note that I am giving this a name that will instruction the user as to what to do.

Step 2 – Find the parameter you just created in the Parameters window.  Right-click on it and choose “Create Calculated Field”.

Step 3 – Use a CASE statement to build the calculated field.  This field is telling Tableau what measure to use based on the value chosen in the “Choose a measure” parameter.

Step 4 – Add Order Date to the column shelf and your new “Measure chosen” measure to the row shelf. For this example, I’ve expanded the Order Date field to Quarter.

Notice how the y-axis is labeled “Measure chosen”.  We’ll clean that up in a bit.

Step 5 – Right-click on your “Choose a measure” parameter in the Parameter window and choose “Show Parameter Control”.  It should appear on the upper-right of the window. 

Make different selections in the parameter control and notice how the y-axis and the chart change dynamically.

Step 6 – Drag the “Choose a measure” parameter to the row shelf.  Again, make different selections in the parameter control and watch the label change.

We’re almost done.  Just a bit of formatting remains.

Step 7 – Clean up the chart.

1. Right-click on the field that shows the “Choose a measure” value and select “Rotate Label”
   
   
   
2. Right-click on the row label and choose “Hide Labels for Rows”.
   
   
   
3. Double-click on the “Measure Chosen” axis to bring up the Edit Axis window.  Delete the title.

That’s it.  You’re final viz should look like this:

If you’re like me, once you saw this technique, you began thinking of all of the possible uses.  Play around with lots of different use cases.  Try scatter plots, dual-axis charts, bubble charts, etc.  You can control ANY of the shelves using parameters.  Imagine the guided analysis you can provide your users and the exploration they’ll be able to do on their own.  It’s almost like creating a pivot table for them. 

If you want a slightly more complex version, I created a scatterplot of NBA franchise values that allows you pick the x-axis, y-axis and size, all with parameters.

Parameters are quite powerful.  Leverage them!

Download the sample workbook here.

Tableau Tip: Using the TOTAL function to summarize dimensions

One of the great benefits of working with brilliant people is that you get challenging questions. When those questions are about Tableau, I’m nearly always convinced that what the person is trying to do can be done.

Wednesday was another great example.  Assume you have a table that measures sales by year by customer.  You’re only interested in customers that have more than $35,000 in sales per year.

And you don’t care who the customers are, just how many.  Restating the question: How many customers have bought over $35,000 of products in a year and what was the total value of those sales?  This is a simple question, but it wasn’t as easy to do in Tableau as I thought.

My initial thought was to simply change the Customers field to a distinct count, but that didn’t work because my individual customers are no longer listed.  Ok, so I need all of the customers, but I don’t want to see them.  Basically I’m looking for one row for each year.  Each row will list year, # of customers over $35K, and total sales for those customers.

Here’s how to do it.  I’m going to assume that you are starting with a view like this.

Step 1 – Create a calculated field to count all of the customers in the view (note that I’m working with an Excel data source, so COUNTD is only available if I make it an extract).

Step 2 – Drop the Customer Count measure onto the data area.  You will see the Customer Count field appear in the Measure Values shelf.

Step 3 – Right-click on the Customer Count measure in the Measure Values shelf and choose Edit Table Calculation.

1. From the Compute Using list, choose Advanced…
2. Move the Year of Order Date and Customer fields to the right and click OK.
   
   
   
3. On the Table Calculation window, change the Restarting every option to Year of Order Date and click OK.
   
   

Your view should now look like this.  Notice how the Customer Count field shows the same total number of Customers in each row for each year.

Step 4 – Create a calculated field to total all of the sales in the view

Step 5 – Drop the Total Sales measure onto the data area. You will see the Total Sales field appear in the Measure Values shelf.

Step 6 – Right-click on the Total Sales measure in the Measure Values shelf and choose Edit Table Calculation.

1. From the Compute Using list, choose Advanced…
2. Move the Year of Order Date and Customer fields to the right and click OK.
   
   
   
3. On the Table Calculation window, change the Restarting every option to Year of Order Date and click OK.
   
   

Your view should now look like this. Notice how the Total Sales field now shows to total sales by year on each row.

Step 7 – Drop the Number of Records measure onto the data area. You will see the Number of Records field appear in the Measure Values shelf.

Step 8 – Right-click on the Number of Records measure in the Measure Values shelf and change the aggregation from Sum to Average.

Step 9 – Right-click on the Number of Records measure in the Measure Values shelf and choose Add Table Calculation.

1. Change the Calculation Type to Running Total.
2. From the Running Along list, choose Advanced…
3. Move the Year of Order Date and Customer fields to the right and click OK.
   
   
   
4. On the Table Calculation window, change the Restarting every option to Year of Order Date and click OK.
   
   

Your view should now look like this. Notice the running total we’ve added and how it restarts every year.  What we’re doing here is simply counting the number of rows in each pane.

Step 10 – Cleanup time!

1. Right-click on the Number of Records pill and choose Filter.  Change the filter to be At Most 1.  This will now keep only the first row in each year. 
   
   
   
   Notice how our Customer Count and Total Sales fields didn’t change. This is because we still have the Customers dimension in the view.
   
2. Drag the Number of Records pill off of the Measure Values shelf.  Be sure to keep it on the filter shelf though.
   
   Note: Steps 7 to 10.1 could all be combined into one step if we put the Number of Records field in the filter immediately.  I didn’t do that because I wanted to demonstrate how we’re leveraging the running totals calculation in this example.
   
3. Move the Customers dimension from the Row shelf to the Level of Detail shelf.
4. Remove Sum of Sales from the view.  It’s no longer relevant.
5. Right click on the two headers for the measures and rename them (Edit Alias).

That’s it!

I have a sneaking suspicion that I’m overlooking a very simple way to do this, so if I am, please let me know.  Either way, this works.

Download the workbook here.

Tableau Tip: Adding totals of a 2nd measure on top of stacked bar charts

Before I get started, credit for the technique/trick in this blog post to Jonathan Drummey.  Follow Jonathan on Twitter or follow his new blog. 

Also, if you don’t use the Tableau Forums for getting answers to those things that have you stumped, then you’re missing out.  It’s very rare that I can’t either (1) find the answer or (2) get the answer by starting a discussion.

Now, consider that you have a stacked bar chart of sales by Product Container color-coded by Region.

Great! But it would be more useful to label the top of each bar with the totals sales like this.

Awesome!  I was banging my head trying to figure out how to make this work.  Adding totals to a stacked bar is straight forward, but the trick here is that the bars represent % of total, not the raw sales.

It seems so simple, but I was stumped.  Fortunately Jonathan was there to save the day, and I can’t believe how simple it was. 

Here’s how it’s done, starting with the stacked bar.

Step 1 – Create a calculated field with the value of one

Step 2 – Drag your new “One” measure onto the right side of the chart until you see the dotted lines (which indicate a dual axis), then drop it.

You now probably have an ugly chart like this:

Step 3 – Change the aggregation for the One measure from Sum to Average (right-click on the One pill, go down to Measure, then pick Average)

Step 4 – Right-click on the right axis and choose Synchronize Axis

Step 5 – Click on the carrot on the top-right of the Marks card and choose Multiple Mark Types

Step 6 – Scroll to the right on the Marks card until you get to SUM(Sales).  Change the mark type to Bar.

Step 7 – Scroll to the right again to get to AVG(One). 

1. Remove Region from the Color shelf
2. Set the color transparency to 0%
3. Change the mark type to Gantt Bar (Joe Mako suggested using Gantt Bar instead of Bar because it creates a line at the top and it will eliminate the chance that the user could select a hidden bar.)
4. Drag the Sales measure onto the Label shelf
5. Set the Label alignment to Top

You should now have a chart that looks like this:

Step 8 – Right-click on the right axis and uncheck Show Header

That’s it!  Pretty simple! (Now that I know how to do it.)

Download the workbook here.

Maybe it’s Nielsen’s fan chart that is causing consumers to be less confident

Of course this chart isn’t the cause, but it did get me heated over its horrific design. 

I’m becoming convinced that Nielsen doesn’t particularly care about making good design decisions, and they apparently don’t particularly value my feedback either.  I can assure you, I’ve given them plenty.

Here’s an alternative that I threw together in Tableau. 

It’s important when creating a visualization that you design it in such a way that, amongst many other things, (1) the reader can see the whole story at a glance, and (2) they can interact for their own deeper analysis.  To accomplish this I:

1. Started with maps (This is a global report with geographic data, which always leads me to start with a map)
2. Made very specific color choices to emphasize the positives (blue) and the negatives (red)
3. Added interactivity through highlighting
4. Included a scatterplot to compare the change to the index
5. Provided the option to filter by continent

Why is all of this important?

Because we need to know how consumers’ feeling are trending; a country could have a high index, but are headed down (e.g. India and China), or a country could have a low index and things are looking up (e.g., South Korea and France).  Those are two very different stories.

Imagine how the politicians could spin this information.  Click on the USA and you’ll see (when you hover over the circle that gets highlighted) that the United States is in trouble.  Their CCI is 87 and it has declined –5 points.  Uh oh!

I challenge you to deduce any of that from Nielsen’s fan chart.

Tableau Tip: Using a parameter for dynamic nested filtering

Suppose you have two dimensions, Customer Segment and Product Sub-Category, and you are displaying sales for each in a bar chart like this:

You want to look at only the top X Product Sub-Categories within each Customer Segment based on Sales.  Naturally, you click on the Sort Descending button, but, uh oh, you get this:

What happened?  The bars are not sorted by Product Sub-Category within each Customer Segment.  They’re actually sorted by the total of the Product Sub-Category across the entire table.

What we really want is for the Product Sub-Categories to be sorted within each Customer Segment.  To do that, you need to create a set.

Step 1 – Choose the two fields you want to create the set with, then right-click and choose Create Set.

Step 2 – Give the set a name if you want, rearrange them to the proper order and click ok. 

NOTE: If the columns are not in the order you want, then simply drag them left and/or right. In our example, we want Customer Segment first because that is our first level dimension.

Step 3 – Drag your new set into the view by placing it between the Customer Segment and Product Sub-Category dimensions on the Rows shelf.

Step 4 – Clear the sort from the Product Sub-Category dimension, then right-click on the Set on the Rows shelf and choose Sort.  Set the sort order to descending by Sales.

Your chart is now sorted by Product Sub-Category within each Customer Segment.

Step 5 – Clean up the chart by right-clicking on your set in the Rows shelf and uncheck Show Header. 

That still leaves some extra grid lines.  To clean them up go to the menu and choose Format => Borders.  Move the slider on the Row Divider level one spot to the left.

You should now have a nice clean bar chart. 

But, I want to see only the top X Product Sub-Categories within each Customer Segment.  Let’s continue on.

Step 6 – Create a parameter to allow the user to choose between the top 1 and the top 10.

Step 7 – Create a “rank” field that will show you the rank of each Product Sub-Category within each Customer Segment.

Step 8 – Add the Rank measure to the Level of Detail shelf.

Step 9 – Edit the Rank field by right-clicking on the Rank field and choosing Edit Table Calculation.  Choose Advanced from the Computing using list.

In the Advanced window, move both fields into the Compute Using box and change the Order Along section to Sales descending.  Click OK.

Now updated the Rank table calculation to the following:

Basically what this is saying is that the Rank field should compute for each Product Sub-Category within a Customer Segment, then it should restart counting when it gets to the next Customer Segment.

Step 10 – Create a calculated field that uses the Top X parameter to filter the number of Product Sub-Categories that should be displayed within each Customer Segment.

This creates a true/false filter.

Step 11 – Add the Top X ? true/false calculated field to the Filter shelf and select True.

Step 12 – Show the Top X parameter control created in Step 6 and you’re done!

You can now use the Top X control to pick how many Product Sub-Categories you want to show within each Customer Segment.

Download the Tableau Workbook here.

Tableau Tip: I’ll take you to the candy shop. I’ll show you how to make a lollipop.

I’ve been gone for a few weeks enjoying some much needed time off before changing jobs and moving to the west coast.  I’m hopping back into the blog saddle with a series of posts about different charts type, their strengths and weaknesses, when to/not to use them, etc. 

The posts will include step-by-step instructions for creating the charts in Tableau.  The instructions for many of these charts have been written before and I will reference the authors whenever I know about their work.

I will typically use a chart I’ve found on the internet in order to provide variety of examples and also to provide a forum for discussing the strengths and weaknesses of their chosen designs.

Let’s get right to it with this lollipop chart from The Washington Post (via Chart Porn):

I like the clean design, the use of simple colors, and the excellent use of data-ink ratio of this chart, but there are few issues that immediately stand out to me.  First though, what is a lollipop chart?

I couldn’t find an exact definition, but I think of a lollipop chart as a combination of a bar chart and a dot plot.  Lollipop charts are great for giving you a sense of both length (bars) and precision (dots). 

However, it only makes sense to use the stick of the lollipop when you’re range starts at zero. 

In the example above, the bars start at 60, therefore including the bars could mislead the reader into thinking the retirement age in Malta is five times lower than Austria. In this example, the bars should be removed, which turns the chart into a dot plot.

Another problem with this chart is that there is no particular rationale to the sort.  At first I thought it was ranked buy retirement age from youngest to oldest, but then Spain was listed before the United States.

For the purpose of the rest of this blog post, we’re going to focus on the retirement age only.

Taking the issues above into account, the data could be represented as a simple dot plot:

Or alternatively as a lollipop chart:

Hopefully at this point you understand when and why you would use a lollipop chart.  In the end, it’s basically a bar chart with a bit more emphasis on the exact value of the bar.  CAUTION: Don’t avoid a bar chart simply because a lollipop chart looks cute.  I would nearly always prefer a bar chart over a lollipop chart.

So now onto the instructions for how to build this chart.  (Credit to Andy Cotgreave, who back in his days at The Data Studio wrote similar instructions)

Step 1 – Drag the Country dimension onto the Rows shelf and the Retirement Age measure onto the Columns shelf.  The result is a bar chart.  Click the sort ascending button.

Step 2 – Drag the Retirement Age measure to the Columns shelf again.  The results is the same bar chart side-by-side.

Step 3 – Right-click on the 2nd Retirement Age measure on the Columns shelf and choose Dual Axis

Step 4 - Right-click on the 2nd Retirement Age measure on the Columns shelf and choose Synchronize Axis

Step 5 - Right-click on the 2nd Retirement Age measure on the Columns shelf and uncheck Show Header

Step 6 – Drag the right side of the chart to the left to shrink the view

Step 7 – On the Marks card, click on the triangle (a.k.a. carrot) on the upper-right and choose Multiple Mark Types

The Marks card should now show “All” at the top and there are now arrows for moving left and right through the measures on the Column shelf, e.g., the two Retirement Ages measures.

Step 8 – Click the right arrow on the Marks card once, then:

1. Change the format of the chart from Automatic to Bar
2. Move the Size slider all the way to the left to make the bars as small as possible
3. Remove the Measure Names field from the Color shelf
4. Change the color of the bar by clicking on the colored square.

Step 9 – Click the right arrow on the Marks card again to move to the 2nd Retirement Age field, then:

1. Change the format of the chart from Automatic to Circle
2. Remove the Measure Names field from the Color shelf
3. Drag the Retirement Age measure onto the Label shelf
4. Format the Label to the font color of your choice and set the horizontal alignment to Center to place the label in the middle of the circle
5. Change the color of the circle by clicking on the colored square
6. Resize the circle

Voila! You’re lollipop chart is complete.  After you practice these steps a few times, you’ll be able to build it in under one minute…guaranteed!

Download the Tableau Workbook here.