Tableau Padawan - When the Pill You See is Not the Pill You Use

/

Today’s lesson is about understanding Tableau as a data driven drawing engine: change the data and we’ll change what Tableau draws. In this case we’re going to change the data to change the color. Recently I received a question where someone had created a heatmap with a diverging color palette like this (demo built using Tableau’s CoffeeChain sample data):

screen-shot-2016-09-24-at-11-57-29-am



They wanted to give their end-users a chance to set the top end of the range so they could better highlight differences in the lower end of the range. We can’t just type in a new range on the color legend, and Tableau’s Edit Colors dialog doesn’t do anything like use a parameter to dynamically set the End of the range:

screen-shot-2016-09-24-at-11-59-42-am

So it looks like we’re stuck…but this being a Tableau Padawan post, we’re not stuck, we just need to look at things differently. Let’s look back at the view above, particularly the pills in the view: There’s Quarter(Date) and Product as dimensions, and SUM(Sales) on both Text and Color. The colors in the heat map are ultimately derived from the SUM(Sales) pill on Color, so the key insight is that even though Tableau’s controls won’t let us get the result we want all we have to do is change the pill on the Color Shelf. We need a pill that returns either the value of Sales or a user-entered value. We can do that with a calculated field. Calculated fields are effectively adding new columns of values to the data and it’s through those custom new data elements that we can change what Tableau is drawing for us.

For this we can use two parameters for the user controls and a calculated field for the color. One parameter to control whether a custom value is used for the top end of the color, another parameter sets the value, and finally a calculated field is used on the Color shelf. The Change Top End of Color? is a Boolean parameter with aliases of Yes and No. This is faster for Tableau to process than a a string-based parameter:

screen-shot-2016-09-24-at-12-00-54-pm

The Top End of Color parameter is a floating point (decimal number) that has a default value of 10,000:

screen-shot-2016-09-24-at-12-01-08-pm

I’ll walk through the Sales Color calculated field, here’s the formula:

IF [Change Top End of Color?] THEN

MIN(SUM([Sales]), [Top End of Color]) ELSE

SUM([Sales]) END

Since the [Change Top End of Color?] is a boolean that returns True or False we can just evaluate the parameter in the IF statement instead of something like “IF [Change Top End of Color?] = ‘Yes’ THEN … ELSE …”. This is simpler to write. Another advantage of using a boolean value and aliases is that we can change the alias without having to change the calculated, for example instead of “Yes” for an alias we might make it a more explicit “Use Top End”. (I'm a big fan of minimizing the amount of effort I have to go through anytime I change a viz).

The MIN(SUM([Sales]), [Top End of Color]) might look strange since it looks like it’s doing an aggregation of an aggregation. In fact MIN() and MAX() are dual-use functions:

  1. The most common use is to get the minimum or maximum of the given record-level value in the current context.
  2. The additional use of MIN() and MAX() that we are using here is to get the lower (or upper) of the two given values.

So in this case the MIN(SUM([Sales]), [Top End of Color]) is going to return the lower value of SUM([Sales]) or the [Top End of Color] parameter. When used on Color this changes the color ramp that Tableau is using so with this particular palette the darkest blue will be used for either the largest value of SUM([Sales]) or (when the Change Top End of Color? parameter is set to Yes) everything from the value of Top End of Color and larger.

Now we can create a view with the two parameters and the new Sales Color measure on the Color Shelf and the view changes:

screen-shot-2016-09-24-at-12-01-17-pm

In this case using the parameters really highlights the lower performance of Amaretto, Caffe Latte, Green Tea, Mint, and Regular Espresso, which was the original goal of changing the color ramp.

Here’s the change top end of color workbook on Tableau Public.

Conclusion

There are three principles at work in this Tableau Padawan lesson:

  1. The main elements of what we see in a Tableau view (number of marks, their position, size, color, shape, text, etc.) are determined by the pills in the view. I like to call the pill layout and pill types (discrete measure, discrete dimension, continuous measure, continuous dimension) the “view specification”.
  2. If we change the pills in the view we’ll change the display of the view - in short, “change the pills, change the view”.
  3. We can use calculated fields to create new pills.

When we adjust our mental model to incorporate these principles then we can become more of an artist, craftsperson, or engineer (pick your metaphor) where we are starting with raw materials (the data), adding our own new elements (calculations), using a tool (Tableau) to build the final piece (the view)!

If this is useful and you want more, get in touch!  Our Tableau trainings are chock-full of insights like these.