Categories
How-To's It Depends Tableau Techniques

It Depends: KPI Swapping with BAN Selectors

Welcome to another installment of “It Depends”. In this post we’re going to look at two different ways to use BAN’s to swap KPI’s in your dashboard. If you’re not familiar with the term “BANs”, we’re talking about the large summarized numbers, or Big Ass Numbers, that are common in business dashboards.

When I build a KPI dashboard, I like to give my users the ability to dig into each and every one of their key metrics, and the techniques we cover in this post are a great way to provide that kind of flexibility. Here is a really simple example of what we’re talking about.

A gif demonstrating how the measures change when each BAN is selected

In the dashboard above, we have 4 BAN’s across the top; Sales, Quantity, Profit, and Profit Ratio. Below that, we have a bar chart by Sub-Category, and a Line Chart showing the current vs previous year. When a user clicks on any of the BANs in the upper section, the bar chart and the line chart will both update to display that metric. A couple of other things that change along with the metric are the dashboard title, the chart titles, and the formatting on all of the labels.

We’re going to cover two different methods for building this type of flexible KPI dashboard. A lot of what we cover is going to be the same, regardless of which method you choose, but there are some pretty big differences in how both the BANs and the Dashboard Actions are constructed in each method.

For this exercise we’re going to use Superstore Data, so you can connect to that source directly in Tableau Desktop. If you would like to follow along in the Sample Workbook, you can download that here.

The Two Methods

Measure Names/Values – In the first method we’re going to use Measure Names and Measure Values to build our BANs. When a user clicks on one of the Measure Values, we will have a dashboard action that passes the Measure Name to a parameter.

Individual BANs – In the second method, we’re going to use separate worksheets for each of one of our BANs. When a user clicks on one of the BANs, we’ll pass a static value that identifies that metric (similar to the Measure Name) to a parameter. With this method, we’ll need a separate dashboard action for each of our BANs.

Method Comparison

So at this point you may be wondering, why would you waste time building out separate worksheets and separate dashboard actions when it can all be done with a single sheet and a single action. Fair question. As you’re probably aware, Measure Names and Measure Values cannot be used in calculated fields, so with the Measure Names/Values method, you are going to be pretty limited in what you can do with your BANs. Let’s take another look at the BANs in the example dashboard from earlier.

An image of BANs with growth indicators and color applied

Numbers alone aren’t always very helpful. It’s important to have context, something to compare those numbers to. Anytime I put a BAN on a dashboard, I like to add some kind of indicator, like percent to a goal, or growth versus previous periods. Another thing I like to do is to use color to make it very obvious which metric is selected and being displayed in the rest of the dashboard. Neither of these are possible with the first method as they both require calculated fields that reference either the selected measure or the value of that measure.

Unlike some of our other “It Depends” posts, the decision here is pretty easy.

A decision tree for which method to use. If you want to add anything other than the measure to your BAN, or want to apply color to show the selection, use Method 2, otherwise you can use Method 1

Method 2 does take a little more time to set up, but in my opinion, it’s usually the way to go. Beyond the two decision points above, the second method also provides a lot more flexibility when it comes to formatting. But if you’re looking for something quick and these other considerations aren’t all that important to you or your users, by all means, go with the first one.

Methods in Practice

This section is going to focus only on building the BANs and setting up the dashboard actions. We’ll walk through how to do that with both of the methods first, and then we’ll move onto setting up the rest of the dashboard, since those steps will be the same for both methods.

Before we get started, let’s build out just a couple of quick calculations that we’ll be using in one or both methods.

First, let’s calculate the most recent date in our data source. Often, in real world scenarios, you’ll be able to use TODAY() instead of the most recent date, but since this Superstore Data only goes through the end of 2021, we’re going to calculate the latest date.

Max Date: {FIXED : MAX([Order Date])}

Now, let’s calculate the Relative Year for each date in our data source. So everything in the most recent year will have a value of 0, everything in the previous year will have a value of -1, and so on.

Relative Year: DATEDIFF(“year”,[Max Date],[Order Date])

And lastly, we’re working with full years of data here, but that’s usually not the case. In my BANs, I want to be able to show a Growth Indicator, but in a real world scenario, that growth should be based on the value of that metric at the same point in time during the previous year. So let’s build a Year to Date Filter.

Year to Date Filter: DATEDIFF(“day”,DATETRUNC(“year”,[Order Date]),[Order Date])<=DATEDIFF(“day”,DATETRUNC(“year”,[Max Date]),[Max Date])

And that calculation is basically just calculating the day of the year for each order (by comparing the order date to the start of that year), and then comparing it to the day of the year for the most recent order. Again, in a real world scenario, you would probably use TODAY() instead of the [Max Date] calculation.

And finally, we just need one parameter that will store our selection when we click on any of the BANs. For this, just create a parameter, call it “Selected Metric”, set the Data Type to “String”, and set the Current Value to “Sales”.

An image showing what the Parameter settings should look like

Ok, that’s enough for now, let’s start building.

Measure Names/Values

Follow the steps below to build your BANs using the Measure Names/Values method. I’m going to provide the steps on how the ones in the sample dashboard were built, but feel free to format however you would like.

Building

  • Right click on [Relative Year] and select “Convert to Dimension”
  • Drag [Relative Year] to filter shelf and filter on 0 (for current year)
  • Drag Measure Names to Columns
  • Drag Measure Values to Text
  • Drag Measure Names to Filter and select Sales, Quantity, Profit, and Profit Ratio
  • Right click on Measure Names on the Column Shelf and de-select “Show Header”
  • Drag Measure Names to “Text” on the Marks Card

Formatting

  • Change Fit to “Entire View”
  • Click on “Text” on the Marks Card and change the Horizontal Alignment to Center
  • Click on “Text” on the Marks Card, click on the ellipses next to “Text” and format
    • Position Measure Names above Measure Values and set font size to 12
    • Change font size of Measure Values to 28
    • Set desired color
  • On the Measure Values Shelf, below the Marks Card, right click on each Measure and format appropriately (Currency, Percentage, etc.)
  • Go to Format > Borders and add Column Dividers (increase Level to get dividers between BANs)
  • Click on Tooltip on the Marks Card and de-select all checkboxes to “turn off”

When you’re done building and formatting your BANs, your worksheet should look something like this

An image showing what the BANs worksheet should look like with Method 1

Now we just need to add this to our dashboard, and then add a a Parameter Action that will pass the Measure Name from our BANs worksheet to our [Selected Metric] parameter.

  • Go to Dashboard > Actions and click “Add Action”
  • Select “Change Parameter” when prompted
  • Give your Parameter Action a descriptive Name
  • Under Source Sheets, select the BANs worksheet that you created in the previous steps
  • Under Target Parameter, select the “Selected Metric” parameter we created earlier
  • Under Source Field, select “Measure Names”
  • Under Aggregation, select “None”
  • Under Run Action on, choose “Select”
  • Under Clearing the Selection Will, select “Keep Current Value”

The Parameter Action should look something like this.

An image showing what the Parameter Action settings should look like

One last formatting recommendation that I would make is to use one of the methods described in this post, to remove the blue box highlight when you click on one of the BANs. Use either the Filter Technique, or the Transparent Technique.

So that’s it for this method…for now. We’re going to switch over to setting up the BANs and dashboard actions for Method 2 first, and then we’ll regroup and walk through the rest of the dashboard setup. If you plan on using Method 1, please skip ahead to the “Setting up the Dashboard” section below.

Individual BANs

Follow the steps below to build your BANs using the Individual BANs method. I’m going to walk through how to build one of the BANs, and then you’ll need to repeat that process for each one in your dashboard. A couple of other things we’ll do in these BANs include adding growth indicators vs the previous year, and adding color to show when that BAN’s measure is selected/not selected. And as I mentioned in the previous example, I’m going to cover how I formatted these BANs in the sample workbook, but feel free to format however you see fit.

Let’s start by building our “Sales” BAN.

Building

  • Right click on [Relative Year] and select “Convert to Dimension”
  • Drag [Relative Year] to filter shelf and filter on 0 and -1 (for current and prior year)
  • Drag [Year to Date Filter] to filter shelf and filter on True
  • Drag Relative Year to Columns and make sure that 0 is to the right of -1
  • Drag your Measure (Sales) to Text on the Marks Card
  • Add Growth Indicator
    • Drag your Measure (Sales) to Detail
    • Right click and select “Add Table Calculation”
    • Under Calculation Type, select “Percent Difference From”
    • Next to “Relative To”, make sure that “Previous” is selected
    • Drag the Measure with the Table Calculation from Detail onto Text on the Marks Card
  • Right click on the “-1” in the Header and select “Hide”
  • Right click on Relative Year on the Column Shelf and de-select “Show Header”

Formatting

  • Change Fit to “Entire View”
  • Click on “Text” on the Marks Card and change the Horizontal Alignment to Center
  • Click on “Text” on the Marks Card, click on the ellipses next to “Text” and format
    • Insert a line above your Measure and add a label for it (ex. “Sales”). Set font size to 12.
    • Change font size of the measure (ex. SUM(Sales)) to 28
    • Change font size of growth indicator (ex. % Difference in SUM(Sales)) to 14
  • Right click on your measure on the Marks Card and format appropriately (for Sales, set to Currency)
  • Right click on your growth measure on the Marks Card, select Format, select Custom, and then paste in the string below
    • ▲ 0.0%;▼ 0.0%; 0%
    • When the growth is positive, this will display an upward facing triangle, along with a percentage set to 1 decimal point
    • When the growth is negative, this will display a downward facing triangle, along with a percentage set to 1 decimal point
    • When there is 0 growth, this will display 0% with no indicator
  • Click on Tooltip on the Marks Card and de-select all checkboxes to “turn off”

When you’re done building and formatting your Sales BAN, it should look something like this.

An image showing what the Sales BAN worksheet should look like

There are a couple more additional steps before we move on to the dashboard actions. First, we need a field that we can pass from this BAN to our parameter. For this, just create a calculated field called “Par Value – Sales”, and in the calculation, just type the word “Sales” (with quotes).

An example of the Par Value calculation

Par Value Sales: “Sales”

And then drag the [Par Value – Sales] field to Detail on your Sales BAN worksheet.

Just a quick note here. If I was building this for a client, I would probably use a numeric parameter, and pass a number from this BAN instead of a text value. It’s a little cleaner and better for performance, but for simplicity and continuity, we’ll use the same parameter we used in Method 1. Ok, back to it.

Now, we need one more calculated field to test if this measure is the currently selected one. This is just a simple boolean calc, and we’ll call it “Metric Selected – Sales”.

Metric Selected – Sales: [Selected Metric]=”Sales”

Now drag that field to Color on your Sales BAN worksheet. Set the [Selected Metric] Parameter to “Sales” (so the result of the calculation is True) and assign a Color. Now, set the [Selected Metric] Parameter to anything else (so the result of the calculation is False) and assign a color.

Now our Sales BAN is built, we just need to add it to our dashboard and then add a Parameter Action that will pass our [Par Value – Sales] field to our [Selected Metric] parameter when a user clicks on the Sales Ban.

  • Go to Dashboard > Actions and click “Add Action”
  • Select “Change Parameter” when prompted
  • Give your Parameter Action a descriptive Name
  • Under Source Sheets, select the Sales BAN worksheet that you created in the previous steps
  • Under Target Parameter, select the “Selected Metric” parameter we created earlier
  • Under Source Field, select [Par Value – Sales]
  • Under Aggregation, select “None”
  • Under Run Action on, choose “Select”
  • Under Clearing the Selection Will, select “Keep Current Value”

The Parameter Action should look something like this.

An image showing what the parameter action settings should look like

And just like with Method 1, I would recommend using one of the methods described in this post, to remove the blue box highlight when you click on the Sales BAN. You could use either the Transparent or the Filter Technique, but with this method, I would really recommend using the Filter Technique.

Now, repeat every step from the “Individual BANs” header above to this step, for each of your BANs. I warned you it would take a little longer to set up, but it’s totally worth it. And once you’re comfortable with this technique, it moves very quickly. To save some time, you can probably duplicate your Sales BAN worksheet and swap out some of the metrics and calculations, but be careful you don’t miss anything.

Setting up the Dashboard

Now our BANs are built and our dashboard actions are in place. Either method you chose has brought you here. We just have a few steps left to finish building our flexible KPI dashboard. Here’s what we’re going to do next.

  • Adjust our other worksheets to use the selected metric
  • Dynamically format the measures in our labels and tooltips
  • Update our Headers and Titles to display the selected metric

Show Selected Metric in Worksheets

The first thing we need to do here is to create a calculated field that will return the correct measure based on what is in the parameter. So users will click on a BAN, let’s say “Sales”. The word “Sales” will then get passed to our [Selected Metric] parameter. Then our calculation will test that parameter, and when that parameter’s value is “Sales”, we want it to return the value of the [Sales] Measure. Same thing for Quantity, Profit, etc. So let’s create a CASE statement with a test for each of our BAN measures, and call it “Metric Calc”.

Metric Calc

CASE [Selected Metric]
WHEN “Sales” then SUM([Sales])
WHEN “Quantity” then SUM([Quantity])
WHEN “Profit” then SUM([Profit])
WHEN “Profit Ratio” then [Profit Ratio]
END

Now, we just need to use this measure in all of our worksheets, instead of a static measure. In our Bar Chart, we’re going to drag this measure to Columns. In our Line Chart, we’re going to drag this measure to Rows.

An image showing the Metric Calc field being used in the dynamic charts in the dashboard

Now, whenever you click on a BAN in the dashboard, these charts will reflect the measure that you clicked on. Pretty cool right? But there is a glaring problem that needs to be addressed.

Dynamic Formatting on Labels/Tooltips

In our example, we have 4 possible measures that could be viewed in the bar chart and line chart; Sales, Quantity, Profit, and Profit Ratio. So 4 possible measures, with 3 different number formats.

  • Sales = Currency
  • Quantity = Whole Number
  • Profit = Currency
  • Profit Ratio = Percentage

At the time of writing this post, Tableau only allows you to assign one number format per measure. But luckily, as with all things Tableau, there is a pretty easy way to do what we want. We’re going to create one calculated field for each potential number format; Currency, Whole Number, and Percentage.

Metric Label – Currency: IF [Selected Metric]=”Sales” or [Selected Metric]=”Profit” then [Metric Calc] END

Metric Label – Whole: IF [Selected Metric]=”Quantity” then [Metric Calc] END

Metric Label – Percentage: IF [Selected Metric]=”Profit Ratio” then [Metric Calc] END

Here’s how these calculations are going to work together. When a user selects;

  • Sales
    • [Metric Label – Currency] = [Metric Calc]
    • [Metric Label – Whole] = Null
    • [Metric Label – Percentage] = Null
  • Quantity
    • [Metric Label – Currency] = Null
    • [Metric Label – Whole] = [Metric Calc]
    • [Metric Label – Percentage] = Null
  • Profit
    • [Metric Label – Currency] = [Metric Calc]
    • [Metric Label – Whole] = Null
    • [Metric Label – Percentage] = Null
  • Profit Ratio
    • [Metric Label – Currency] = Null
    • [Metric Label – Whole] = Null
    • [Metric Label – Percentage] = [Metric Calc]

No matter what BAN is selected, ONE of these calculations will return the appropriate value and TWO of these calculations will be Null. In Tableau, Null values do not occupy any space in labels and tooltips. So all we need to do is line up these three calculations together. The two Null values will collapse, leaving just the populated value. Here’s how you do that.

  • Go to the Metric by Sub-Category sheet (or one of the dynamic charts in your workbook)
  • If [Metric Calc] is on Label, remove it
  • Format your measures
    • Right click on [Metric Label – Currency] in the Data Pane, select “Default Properties” and then “Number Format”
    • Set the appropriate format
    • Repeat for [Metric Label – Whole] and [Metric Label – Percentage]
  • Drag all 3 [Metric Label] fields to “Label” on the Marks Card
  • Click on “Label” on the Marks Card and click on the ellipses next to “Text”
  • Align all of the [Metric Label] fields on the first row in the Edit Label box, with no spaces between fields (see image below)

Your Label should look like this.

An image showing the correct layout, with all of the dynamic labels next to each other

If all of your calculations and default formatting are correct, your Chart labels should now be dynamic. When you click on Sales or Profit, your labels should show as currency. When you click on Quantity, your labels should show as whole numbers. And when you click on Profit Ratio, your labels should show as Percentages. You can repeat this same process for all of your Labels and Tooltips.

Displaying Parameter in Titles

Saving the easiest for last! One last thing you’ll want to do is to update your chart titles so that they describe what is in the view. The view is going to be changing, so the titles need to change as well. Luckily this is incredibly easy.

  • Double click on any chart title, or text box being used as a title
  • Place your cursor where you would like the [Selected Metric] value to appear
  • In the top right corner, select “Insert”
  • Choose the [Selected Metric] parameter

It should look like this. Just repeat for all of your other titles (can work in Tooltips as well).

An image demonstrating how to insert the parameter into a chart title

Finally, I think we’re done! Those are two different methods for building really flexible KPI dashboards. This is a model that I use all the time, and users always love it! It’s incredibly powerful and just a really great way to let your users explore their data without having to build different dashboards for each of your key indicators.

As always, thank you so much for reading, and see you next time!

Categories
How-To's Tableau Techniques

Robinson Projection in Tableau

When it comes to visualizing data, it’s no secret that the Mercator Projection has it’s issues. Certain countries, especially in the northern hemisphere, appear much larger than they are in reality, and it gets worse the farther you move from the equator. Just look at the difference in the size of Greenland between the Mercator and Robinson projections.

That’s because this particular projection was created for one specific purpose…to aid in navigation. And it is perfect for that purpose, but not so perfect for visualizing data. But when it comes to Tableau, it’s our only option…or is it?

I’d like to use this opportunity to point out that I did not come up with the methods discussed in this blog post, but they are techniques that I use pretty frequently, and something I get asked about a lot. So the goal of this post is to combine information from various sources, provide you with all of the files needed to create your own Robinson projections, and walk through, step by step, multiple approaches to building these maps in Tableau. For more information, I recommend checking out this blog post by Ben Jones, and this post by John Emery

I also want to use this opportunity to point out that these methods are more complicated, and less performant than using Tableau’s standard mapping, so I would recommend using them with caution.

Different Methods

This post is going to cover three different methods for creating Robinsons Projection Maps in Tableau.

  • Using a Shapefile
  • Using a CSV File (for countries)
  • Using a CSV File (for cities)

You can find all of the files needed for these methods here, as well as a sample workbook with all of the maps built.

Method 1: Using a Shapefile

This is by far the easiest method, but it’s a little less flexible than using a CSV File. To get started, download the following 4 files from the link above, and place them all in the same folder somewhere on your machine.

  • Country_ShapeFile_Robinson.DBF
  • Country_ShapeFile_Robinson.PRJ
  • Country_ShapeFile_Robinson.shp
  • Country_ShapeFile_Robinson.SHX

Now, let’s set up our data source

Building the Data Source

One quick note before we start. This particular Shapefile only has the 2-digit codes for countries. In order to relate this file to your data, you’ll need to make sure that your source also contains these codes (would not recommend trying to relate on country name). You can find a full list of codes here. Now let’s build our data source.

  • Connect to your Data
    • If you do not have data and just want to practice these techniques, you can use the Happiness_Scores.xlsx (from the World Happiness 2022 Report) file in the Google Drive
  • Connect to the Shapefile
    • From the Data Source Page, click “Add” at the top left to add a new connection
    • In the “To a File” options, select “Spatial File”
    • Navigate to the location where you stored the files above and select the Country_ShapeFile_Robinson.shp file
    • Drag the Shapefile into the data source pane
  • Update the relationship
    • Click on the noodle connecting the two sources
    • In the lower section, select the country code in each file (called ISO in Shapefile)

When complete, your data source should look like this

Building the View

  • Create a new worksheet
  • Double-click on the [Geometry] field
  • Add the 2-digit country code from your primary source onto Detail (in the sample data it’s called ISO A2)
  • Add whatever measure you would like to Color and assign your desired palette (in this example, I placed [Happiness Score] on color and assigned a Viridis palette)

When complete, your worksheet should look something like this.

Well, that’s a bit strange, isn’t it? Here’s an extremely quick explanation of what’s happening. Tableau only supports the Mercator projection. Also, in Tableau, you are limited to just the Map Mark Type for Shapefiles. When you add the [Geometry] field, Tableau generates the latitude and longitude for each polygon in your Shapefile and plots them on the map. But the coordinates that make up those polygons in our Shapefile are for a Robinson projection. So here you can see our Robinson Projection overlaid on Tableau’s Mercator Projection. There’s some magic happening behind the scenes in those Shapefiles that allows this to happen. You can read more about it in John’s post.

So now, let’s get rid of the Mercator Projection (or at least hide it).

First, you’ll want to remove the “Search” Map option. This search is based on the positions of countries in the Mercator Projection, not our new Robinson Projection, so if you try to search for a country, it’s going to bring you to the wrong place. I’d recommend disabling all of the Map options, but that’s up to you

  • In the upper toolbar, click on Map
  • Click on Map Options
  • Deselect the box labelled “Show Map Search”
  • Optional – Deselect all other options

Next we’ll want to remove all of the Map Layers

  • In the upper toolbar, click on Map
  • Click on Map Layers
  • Deselect all of the boxes in the Map Layers Window along the left side of the screen

The Map Layers Window should look like this

Finally, let’s remove our Worksheet Shading, just in case we want to put this on a colored dashboard, or lay it over a background image

  • In the upper toolbar, click on Format
  • Click on Shading
  • Change the first option (on the Sheet tab, under Default, labelled “Worksheet”) from White to None

And that should do it! At this point, your map should look something like this.

Wait a second…something’s still not right. Antarctica has no permanent population, so how did they respond to the World Happiness Survey? Well, they didn’t. In fact, there are several countries that are showing up on this map as yellow that aren’t in the World Happiness data. But because we’re using relationships, and because these countries exist in the Shapefile, they are being shown on the map and colored the same as the lowest value in the data source. This can be misleading, so let’s get rid of those.

  • Drag the measure (the same one currently on color) to the filter shelf
  • Click on “Special”
  • Select “Non-Null” Values

Ok, now we should be all set.

I got to be honest, something about this still bothers me. I would love to be able to see the countries that are missing data, but unfortunately, Tableau does not have the capability to ignore null values in the color application. But we have a few options.

Option 1: Create bins off of your measure and assign a color to each bin. Null will have its own bin

Option 2: The same as option 1 but use a calculated field to set thresholds instead of creating bins

Option 3: Duplicate our map worksheet, remove the measure from filter, remove the measure from color, set the color to how we want the “missing” values to appear, and then stack these two maps on top of each other on our dashboard (set to floating and set x, y, w, and h the same). The result would look something like this

Much better! Any of these methods will work, but I’m partial to the 3rd. This is mainly because you can use a diverging or continuous color palette instead of having to figure out which colors to assign to each “bin” with Option 1 and 2.

Now let’s move on to the CSV File method

Method 2: Using a CSV File (for countries)

This method has a lot of similarities to the first one, so I won’t go in to too much detail. The two main differences are; we are going to use a csv instead of a shapefile, and we are going to use the polygon mark type instead of a map.

To get started, download the CountryShapes.csv file from the Google Drive.

Building the Data Source

We’re going to set up our data source the same way as we did in Method 1. Connect to your data, add a connection for the csv file, and set up the relationship. This csv file, unlike the shapefile, has a ton of different country identifier fields. Do some prep work in advance to figure out which of these identifiers match what is in your data source, and set up the relationship using that field. When you’re finished, it should look something like this.

Building the View

Building the view is a little more complicated with this method. This csv file has the coordinates to “draw” all of the countries. But take a look at what happens if we try to use those coordinates as-is.

Now that looks an awful lot like a Mercator Projection. Well, that’s because it is. This file has the all of the coordinates to “draw” each country, but they are based on the Mercator Projection. So we need to re-calculate those latitude and longitude values. We’re going to build two new calculated fields, R_Lat (for our new latitude), and R_Lon (for our new longitude).

R_Lat

IF [Latitude]=0 THEN [Latitude]

ELSEIF ABS([Latitude])<5 THEN 0.5072 * (0+(0.0620-0) * ((ABS([Latitude])-0)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<10 THEN 0.5072 * (0.0620+(0.1240-0.0620) * ((ABS([Latitude])-5)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<15 THEN 0.5072 * (0.1240+(0.1860-0.1240) * ((ABS([Latitude])-10)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<20 THEN 0.5072 * (0.1860+(0.2480-0.1860) * ((ABS([Latitude])-15)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<25 THEN 0.5072 * (0.2480+(0.3100-0.2480) * ((ABS([Latitude])-20)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<30 THEN 0.5072 * (0.3100+(0.3720-0.3100) * ((ABS([Latitude])-25)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<35 THEN 0.5072 * (0.3720+(0.4340-0.3720) * ((ABS([Latitude])-30)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<40 THEN 0.5072 * (0.4340+(0.4958-0.4340) * ((ABS([Latitude])-35)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<45 THEN 0.5072 * (0.4958+(0.5571-0.4958) * ((ABS([Latitude])-40)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<50 THEN 0.5072 * (0.5571+(0.6176-0.5571) * ((ABS([Latitude])-45)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<55 THEN 0.5072 * (0.6176+(0.6769-0.6176) * ((ABS([Latitude])-50)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<60 THEN 0.5072 * (0.6769+(0.7346-0.6769) * ((ABS([Latitude])-55)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<65 THEN 0.5072 * (0.7346+(0.7903-0.7346) * ((ABS([Latitude])-60)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<70 THEN 0.5072 * (0.7903+(0.8435-0.7903) * ((ABS([Latitude])-65)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<75 THEN 0.5072 * (0.8435+(0.8936-0.8435) * ((ABS([Latitude])-70)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<80 THEN 0.5072 * (0.8936+(0.9394-0.8936) * ((ABS([Latitude])-75)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<85 THEN 0.5072 * (0.9394+(0.9761-0.9394) * ((ABS([Latitude])-80)/5)) * SIGN([Latitude])

ELSEIF ABS([Latitude])<90 THEN 0.5072 * (0.9761+(1-0.9761) * ((ABS([Latitude])-85)/5)) * SIGN([Latitude])

END

R_Lon

IF [Latitude]=0 THEN [Longitude]

ELSEIF ABS([Latitude])<5 THEN [Longitude] * (1-((ABS([Latitude])-0)/5) * (1-0.9986))

ELSEIF ABS([Latitude])<10 THEN [Longitude] * (0.9986-((ABS([Latitude])-5)/5) * (0.9986-0.9954))

ELSEIF ABS([Latitude])<15 THEN [Longitude] * (0.9954-((ABS([Latitude])-10)/5) * (0.9954-0.9900))

ELSEIF ABS([Latitude])<20 THEN [Longitude] * (0.9900-((ABS([Latitude])-15)/5) * (0.9900-0.9822))

ELSEIF ABS([Latitude])<25 THEN [Longitude] * (0.9822-((ABS([Latitude])-20)/5) * (0.9822-0.9730))

ELSEIF ABS([Latitude])<30 THEN [Longitude] * (0.9730-((ABS([Latitude])-25)/5) * (0.9730-0.9600))

ELSEIF ABS([Latitude])<35 THEN [Longitude] * (0.9600-((ABS([Latitude])-30)/5) * (0.9600-0.9427))

ELSEIF ABS([Latitude])<40 THEN [Longitude] * (0.9427-((ABS([Latitude])-35)/5) * (0.9427-0.9216))

ELSEIF ABS([Latitude])<45 THEN [Longitude] * (0.9216-((ABS([Latitude])-40)/5) * (0.9216-0.8962))

ELSEIF ABS([Latitude])<50 THEN [Longitude] * (0.8962-((ABS([Latitude])-45)/5) * (0.8962-0.8679))

ELSEIF ABS([Latitude])<55 THEN [Longitude] * (0.8679-((ABS([Latitude])-50)/5) * (0.8679-0.8350))

ELSEIF ABS([Latitude])<60 THEN [Longitude] * (0.8350-((ABS([Latitude])-55)/5) * (0.8350-0.7986))

ELSEIF ABS([Latitude])<65 THEN [Longitude] * (0.7986-((ABS([Latitude])-60)/5) * (0.7986-0.7597))

ELSEIF ABS([Latitude])<70 THEN [Longitude] * (0.7597-((ABS([Latitude])-65)/5) * (0.7597-0.7186))

ELSEIF ABS([Latitude])<75 THEN [Longitude] * (0.7186-((ABS([Latitude])-70)/5) * (0.7186-0.6732))

ELSEIF ABS([Latitude])<80 THEN [Longitude] * (0.6732-((ABS([Latitude])-75)/5) * (0.6732-0.6213))

ELSEIF ABS([Latitude])<85 THEN [Longitude] * (0.6213-((ABS([Latitude])-80)/5) * (0.6213-0.5722))

ELSEIF ABS([Latitude])<90 THEN [Longitude] * (0.5722-((ABS([Latitude])-85)/5) * (0.5722-0.5322))

END

Ok, so those calculations are a bit intense. But luckily, they are the only calculations that we need. Now let’s build our Robinson Projection.

  • Right click on [R_Lon] and drag it to Columns. When prompted, choose R_Lon without any aggregation
  • Right click on [R_Lat] and drag it to Rows. When prompted, choose R_Lat without any aggregation
  • Right click on your measure (Happiness score in the sample data) and drag it to Color. Choose MIN() when prompted
  • Drag [Polygon ID] to Detail
  • Drag [Sub Polygon ID] to Detail
  • Right click on [Point ID] and drag it to Path. When prompted, choose Point ID without any aggregation
  • Right click on the R_Lat axis, select “Edit Axis” and fix the axis to Start at -.6 and end at .6
  • Right click on the R_Lon axis, select “Edit Axis” and fix the axis to Start at -200 and end at 200

It should look something like this

You’ve probably noticed that we have the same problem as we did before. Countries that were not in our data are showing up in the map and colored the same as the lowest value. To remove these, you can follow the same process as we did with the first method (drag measure to filter, click on All Values, click on “Special”, select “Non-null values”). Another way to remove these is to just drag the country field from your data file to Detail.

Now, if we want to view those missing countries, but not apply the color, we could do exactly what we did with the first method (duplicate and stack). Or…we can do something a bit different. Let’s add a background image.

From the Google Drive, download the MapBackground.png file.

Now, let’s add that as a background image to our map

  • In the upper toolbar, click on Map
  • Click on Background Images
  • Select your data source (the same one used for the map)
  • Click “Add Image”
  • Give your background image a name like “Background Map”
  • Click “Browse” and select the MapBackground.png file
  • Under “X Field” make sure R_Lon is selected and set the Left = -180 and the Right = 180
  • Under the “Y Field” make sure R_Lat is selected and set the Left = -.51 and the Right = .52
  • Adjust the “Washout” for a lighter or darker map

The Background Image options should look like this when you’re done

You can also find or create your own background images, you’ll just have to play with them a bit to get them lined up correctly.

At this point, you’re pretty much done, but I would recommend a few finishing touches (similar to the first method)

  • Disable all Map Options
  • Hide the X and Y axes
  • Remove all Lines (Grid lines/Zero lines/etc.)
  • Remove Worksheet Shading (change White to Null)

And now, you’re map should look something like this

Alright, we’re going to cover one more method, but I promise, this one will be short. These two methods are great if you’re mapping countries, but what if you want to map cities?

Method 3: Using a CSV File (for cities)

In order to plot cities on a Robinson Projection, your data source will need to have the Latitude and Longitude values. Don’t have those? Well, let Tableau do the work for you. Here’s a link to a quick tutorial on how to export the generated Latitude and Longitude values from Tableau

How to get latitude and longitude values from Tableau

Once you have those in your data source, connect to your file, and let’s start building. If you don’t have a file, you can download the “OlympicGames_Cities.xlsx” file from our Google Drive.

Building the View

So we have the coordinates for each of our cities, but similar to the last example, if we try to plot these in Tableau, it’s going to plot them using the Mercator Projection. So we are going to use the same R_Lon and R_Lat calculations that we used in the previous method. They’re crazy long, so I’m not going to paste them here, but you can find them earlier in the post.

Once you have those calculations built, the process is nearly identical to Method 2.

  • Right click on [R_Lon] and drag it to Columns. When prompted, choose R_Lon without any aggregation
  • Right click on [R_Lat] and drag it to Rows. When prompted, choose R_Lat without any aggregation
  • Drag City to Detail
  • Right click on the R_Lat axis, select “Edit Axis” and fix the axis to Start at -.6 and end at .6
  • Right click on the R_Lon axis, select “Edit Axis” and fix the axis to Start at -200 and end at 200
  • Make any other design adjustments desired (change mark type to circle, add field to color, adjust size, adjust transparency, etc)

Now, just follow the same exact process from Method 2 to add your Background Map and to put on the finishing touches. And you should have something like this

Well that’s it for this post. I hope you enjoyed it, and as always, please reach out with any questions or to share your creations. We would love to see them. Thanks for reading, and see you next time!

Categories
How-To's It Depends Tableau Techniques

Building an Org Chart in Tableau: Two methods

Many of us have been asked at some point to build an org chart, or something like it, in Tableau. And, like most of you, I started off with some ideas on how it could work, played with it a little bit, and then went to trusty ol’ Google, to see what the community had come up with. And as usual, the community delivered. I found two posts that set me on the right direction, even though they weren’t quite working for what I needed to do. So, credit, and a huge thank you to Jeff Schaffer, for his post on the subject from 2017, and to Nathan Smith for his post.

Starting with the data…

In order to build an org chart, you will need, at minimum — two fields:

  1. Employee
  2. Manager

Ideally you will have unique IDs for these records, and additional information such as departments and titles. But those two fields are all you really need.

Next, you will need to shape your data to create the hierarchical relationships between the Employee, their direct subordinates, and all of their supervisors. There are two approaches you can take to model the data. Whether you can transform the data using Tableau Prep, Alteryx, SQL, etc. will probably be the main factor in the decision. Both methods will produce the same end result from the user’s perspective.

Method 1: Preparing the data outside of Tableau Desktop

Using this method, we will prepare the data in Tableau Prep* to create a table that has one record for each employee-supervisor, and one record for each employee-subordinate relationship. We will then use the output to build the org chart visual in Tableau Desktop.

*I’ve used Prep to demonstrate because it does a nice job of visually showing what is happening, and many Tableau Creators have access to Tableau Prep. You can use the same concepts in your data prep tool of choice.

  • Pro: If the hierarchy becomes deeper, you can make the change once in the workflow and the Tableau dashboard will not need to be updated to scale with your organization. (If using Alteryx or SQL, this can be fully automated)
  • Con: You need the ability and access to use a data preparation tool and refresh the data on a schedule.

Learn how to use this method here >

Method 2: Preparing the data in Tableau Desktop

Using this method, we will create a data source in Tableau Desktop with one record for each employee with one column for each supervisor in the hierarchy, and one record for each employee-subordinate relationship. We will then use the data source to build the org chart visual.

  • Pro: You an do all the data preparation you need right within Tableau Desktop, with no other tools or schedulers necessary.
  • Con: There will be more to update in the event the organizational hierarchy gets deeper.

Learn how to use this method here >

The end result

What I ended up with was an interactive org chart dashboard that thrilled my stakeholders, complete with name search and PDF downloads, and a lot of interactivity. I’ve published a couple of variations with fewer bells and whistles to my Tableau Public profile.

An interactive org chart navigator dashboard:

Org Chart - Interactive

And, a static vertical layout for printing to PDF:

Org Chart - Printable
Categories
How-To's Totally Useless Charts

Totally Useless Charts & How to Build Them – “Hand-drawn” Bar Charts

Welcome to our 2nd installment of Totally Useless Charts & How to Build Them, where we do…exactly what the name implies. Look at some totally useless charts and walk through, step by step, how to build them. If you missed the first installment, the goal of this series isn’t necessarily to teach you how to build these specific useless charts, but more to talk through the techniques, the approach, and the thought process behind each chart, so you can apply those concepts to your own custom charts.

In this installment we’re going to learn how to build “hand-drawn” bar charts in Tableau. These of course aren’t actually hand-drawn, but using some interesting techniques, and a lot of random numbers, we can kind of make them look that way. If you would like to follow along, you can download the workbook here, and the data here.

“Hand-Drawn” Bar Charts

First, let’s look at an example of what we’re talking about. Here is a viz that I published recently about relationships on the show “The Office”. You can check out the interactive viz here.

My goal was to make the entire viz look like an office desk belonging to everyone’s favorite receptionist/office manager, Pam Halpert. To do that, I had to make all of the visualizations appear to be “hand-drawn”, including the bar charts. Let’s zoom in one those.

Here we have two different bar charts, one for the Longest Relationship, and one for the Most Time in Relationships (by number of episodes). Today we’re going to be using different data, but the goal is still the same…build some bar charts that look “hand-drawn”.

Building Your Data Source

Let’s start with our data. For this example we’re going to look at the top 10 highest grossing films of all time. If you downloaded the sample data, you can find these in the “Data” tab.

Next, we need to do some densification. The first thing we need to do is to create a record for every line needed in each bar. If you look at one of these bars closely, you’ll see that it’s actually made up of a bunch of lines…one outer line (orange), and a number of cross lines (blue).

So we are going to densify our data with our first densification table, called “Lines” in the sample data. In this table, we have 1 record for our Outer Line, and 50 records for our Cross Lines

Then we’re going to join our “Data” table, and our “Lines” table using a join calculation with a value of 1 on each side.

But we’re not quite done yet. Now we have a record for each of our lines, but each of those lines is made up of multiple points. Our “Outer Line’ is made up of 4 points, and each of our “Cross Lines” is made up of two points

So we’re going to use one more densification table to create additional records for each of these points, for each of the lines. This table is called “Points” in the sample data

And we’re going to join this to our “Lines” table on the [LineType] Field.

Now for each of our 10 films, we have 4 records for our “Outer Line” and 100 records for our “Cross Lines”, 2 for each of the 50 lines in the “Lines” table.

Drawing the “Outer Lines”

Now we have our data, let’s start building our Totally Useless Chart. We’re going to start with the Outer Lines. To do this, and to make it a bit dynamic so you can play around with how the chart looks, we’re going to build 4 Parameters. Each of these is going to have a Data Type of “Float” and the Default values are below

  • Bar_Width = .6 (used for the height of each bar and the spacing between the bars)
  • Scale_Bar_Outer_Height = .03 (used along with a random number to jitter points vertically)
  • Scale_Bar_Outer_Length = .1 (used along with a random number to jitter points horizontally)
  • Cross Lines = 50 (used to limit the number of cross-lines in each bar. This is optional)

Next, we’ll start building our calculations. The first, and arguably most important of these calcs is going to be our [Jitter] calculation. We want a random number between -1 and 1. The Random() function will give us a random number between 0 and 1, so we can modify that by multiplying the random number by 2 and then subtracting 1 (so a random number of .6 would become .2, and a random number .4 would become -.2)

Jitter = Random()*2-1

Next, we need to calculate the length of each of our “bars”. We’re going to do this by comparing each value to the maximum value and then multiplying it by the Max Length, which in our case will be the number of “Cross” lines we have. So the highest grossing film, Avatar, will have a length of 50, since we have 50 “Cross” lines. ((2.847B/2.847B)*50). Number 10 on the list will have a length of around 26.6 ((1.515B/2.847B(*50). So first, let’s calculate our Max length.

Max Length = {MAX([Line ID]}

Next, we’ll want to divide the Box Office Gross for each movie by the value for the highest grossing movie. The result of this will be a percentage which we’ll then multiply by our [Max Length] field to get our [Outer Bar Length]

Outer Bar Length = ([Box Office Gross]/{MAX([Box Office Gross])})*[Max Length]

Now, we need to calculate the X and Y coordinates for the 4 points of each “Outer Line”. So under normal circumstances, point 1 and point 4 would start at 0, and point 2 and point 3 would just be the [Outer Bar Length]. So if you connected those points, it would start at 0 for point 1, go to the end of the line for point 2, stay at the end of the line for point 3, and then return to 0 for point 4. But we want this to look “hand-drawn”, and if I was drawing bar charts by hand, there is no way they would align that neatly. That’s where our [Jitter] and “Scale” parameters come in.

Outer_Bar_X

CASE [Points]

WHEN 1 then 0+([Jitter]*[Scale_Bar_Outer_Length])

WHEN 2 then [Outer Bar Length]+([Jitter]*[Scale_Bar_Outer_Length])

WHEN 3 then [Outer Bar Length]+([Jitter]*[Scale_Bar_Outer_Length])

WHEN 4 then 0+([Jitter]*[Scale_Bar_Outer_Length])

END

We just want to move these points slightly to get that “hand-drawn” effect, which is why we are using the “Scale” parameters. For that first point, if we just did 0+[Jitter], that value could fall anywhere between -1 and 1, which is a pretty significant shift. But using the [Scale_Bar_Outer_Length] parameter, we can increase that value to get more jitter, or decrease the value to get less jitter. Using a value of .1 in the parameter, means that the value for that first point would now fall somewhere between -.1 and .1.

Next, we need to calculate our Y coordinates for those same 4 points. Again, under normal circumstances, for point 1 we would add half of the bar width to our starting point (the middle of the bar), same for point 2, and then for points 3 and 4, we would subtract half of the bar width from the starting point. So, along with the X coordinates, it would look something like this.

This is where the [Bar_Width] parameter comes into play. We need to know how thick these bars should be. We’re using the [Rank] field as our starting point, so the first bar will start 0,1, the second bar will start at 0,2, and so on. But we don’t want the bars to overlap, or be right up against each other, so we can control that with the parameter. A larger value in this parameter will result in wider bars and less spacing, a smaller value will result in skinnier bars, and more spacing. A value of 1 will result in no spacing between the bars.

Also, similar to the calculation for the X coordinates, we are using that [Jitter] field along with a “Scale” parameter to control how much jitter there will be. So a larger number in the [Scale_Bar_Outer_Height] parameter will result in more vertical jitter, and a lower number will result in less. Here is the calculation for the Y coordinates.

Outer_Bar_Y

CASE [Points]

WHEN 1 then [Rank]+([Bar_Width]/2)+([Jitter]*[Scale_Bar_Outer_Height])

WHEN 2 then [Rank]+([Bar_Width]/2)+(([Jitter]*[Scale_Bar_Outer_Height])2)

WHEN 3 then [Rank]-([Bar_Width]/2)+(([Jitter]*[Scale_Bar_Outer_Height])2)

WHEN 4 then [Rank]-([Bar_Width]/2)+([Jitter]*[Scale_Bar_Outer_Height])

END

Now we have all of the calculations needed to draw our “Outer” lines. So let’s do that

  • Drag [Line Type] to the filter shelf and filter on “Outer Lines”
  • Right click on [Outer_Bar_X], drag it to Columns, and when prompted, choose [Outer_Bar_X] without aggregation
  • Right click on [Outer_Bar_Y], drag it to Rows, and when prompted, choose [Outer_Bar_Y] without aggregation
  • Change the Mark Type to “Line”
  • Right click on [Rank], select “Convert to Dimension” and then drag [Rank] to Detail
  • Right click on [Points], drag it to Path, and when prompted, choose [Points] without aggregation
  • Right click on the Y-axis, select “Edit Axis”, and check the box labelled “Reverse” under Scale

When that’s finished, you should have something that looks like this. There are 10 “bars”, with all 4 points in each bar slightly jittered to give it that “hand-drawn” look.

Next, we need to add the “Cross Lines”

Drawing the “Cross Lines”

To help understand the approach we’re going to take, think about taking each of these bars and breaking them into individual segments. So, for example, our first bar has a length of 50 (think back to the Max Length calculation). So we want to break that into 50 individual segments and draw a diagonal line from the top left of the segment to the bottom right of the segment.

The image above is roughly what it would look like if we draw perfect lines across those segments. But we don’t want perfect lines. We want “hand-drawn” lines. So we’re going to leverage our [Jitter] field and our “Scale” parameters once again.

So let’s build our X and Y calculations. Remember when we built our data source, for our “Cross Lines”, we needed two points, 1 and 2. So Point 1 is going to start the line at the top left of our segment, and Point 2 is going to end the line at the bottom right of our segment. Here is the calculation

Cross_X = if [Points]=1 then [Line ID]-1 + ([Jitter]*[Scale_Bar_Outer_Length]*2) else [Line ID]+([Jitter]*[Scale_Bar_Outer_Length]*2) END

Here we are calculating the position for both points on the X axis. For the first point, when [Points]=1, we want to use our [Line ID] value and subtract 1, so we’re starting at the beginning of our segment (ex. line 1 will start at 0, line 2 will start at 1, line 3 will start at 2, and so on). When [Points]=2, we are going to use just the [Line ID] value (ex. line 1 will end at 1, line 2 will end at 2, line 3 will end at 3). And then we’re just using our [Jitter] field and our “Scale” parameter to jitter these points a little bit, similar to what we did with the “Outer” lines. You may notice that there is a “*2” in these calculations. I added these so I could re-use my same parameters, but could add a little extra jitter to the Cross Lines. I figured if these were actually being done by hand there would be a lot more variation in these lines, compared to the “Outer” lines.

Now let’s calculate our Y coordinates. Similar to how we calculated the Y coordinates for the “Outer” lines, we want one of our points to be half the width of the bar above our starting point, and the other one, half the width of the bar below the starting point. And then we want to jitter them. Here’s the calculation for the Y coordinates.

Cross_Y = if [Points]=1 then [Rank]-([Bar_Width]/2)+([Jitter]*[Scale_Bar_Outer_Height]*2) else [Rank]+([Bar_Width]/2)+([Jitter]*[Scale_Bar_Outer_Height]*2) END

Now this is a little bit confusing because we reversed our axis in an earlier step. So, for Point 1, instead of adding half of the width of the bar to our starting point, the [Rank] field, we need to subtract it from the starting point, to get it to appear above the bar (because the axis is reversed). So when [Points]=1 we’ll subtract half of the width of the bar ([Bar_Width]/2) from the starting point, [Rank]. When [Points]=2, we’ll add half of the width of the bar to the starting point. And then once again we’re using the [Jitter] field, the “Scale” parameter, and then multiplying by 2 to get a little extra jitter. If you wanted to reverse the direction of these lines, so they go from top right to bottom left, just change the calc so when [Points]=1 you add, and when [Points]=2 you subtract.

Now let’s build it.

  • Drag [Line Type] to the filter shelf and filter on “Cross Lines”
  • Right click on [Cross_X], drag it to Columns, and when prompted, choose [Cross_X] without aggregation
  • Right click on [Cross_Y], drag it to Rows, and when prompted, choose [Cross_Y] without aggregation
  • Change the Mark Type to “Line”
  • Right click on [Line ID], select “Convert to Dimension” and then drag [Line ID] to Detail
  • Right click on [Rank], select “Convert to Dimension” and then drag [Rank] to Detail
  • Right click on [Points], drag it to Path, and when prompted, choose [Points] without aggregation
  • Right click on the Y-axis, select “Edit Axis”, and check the box labelled “Reverse” under Scale

Once complete, you should have 50 “Cross Lines” for each of your “bars”.

Now we just need to bring it all together

Combining the Lines

We have our “Outer” lines, and we have our “Cross” lines, and because we have separate data points for each of these (because of the way we structured our data) we can bring them together in the same view pretty easily. We just need to 2 more “Final” calculations for the X and Y coordinates.

Final_X = if [Line Type]=’Outer Lines’ then [Outer_Bar_X] else [Cross_X] END

Final_Y = if [Line Type]=’Outer Lines’ then [Outer_Bar_Y] else [Cross_Y] END

These are pretty straightforward, but basically, if the [Line Type]=”Outer Lines” use the X and Y values from the “Outer_Bar” fields. Otherwise, use the X and Y values from the “Cross” fields. Now let’s build our “bars” with these “Final” calcs.

  • Right click on [Final_X], drag it to Columns, and when prompted, choose [Final_X] without aggregation
  • Right click on [Final_Y], drag it to Rows, and when prompted, choose [Final_Y] without aggregation
  • Change the Mark Type to “Line”
  • Right click on [Line ID], select “Convert to Dimension” and then drag [Line ID] to Detail
  • Right click on [Rank], select “Convert to Dimension” and then drag [Rank] to Detail
  • Drag [Line Type] to Detail
  • Right click on [Points], drag it to Path, and when prompted, choose [Points] without aggregation
  • Right click on the Y-axis, select “Edit Axis”, and check the box labelled “Reverse” under Scale

And now you should have everything together on the same view!

Wait…that doesn’t look right. We don’t want all of those extra “Cross” lines on our shorter bars. Luckily, we can filter those out pretty easily with a calculated field. This is just a boolean calc that checks to see if the Line ID is less than the length of the bar. Remember from earlier that the Line ID corresponds to the right side, or the end of each of these “Cross” lines. So we only want to keep the lines where that value is less than the length of the bar.

Extra Lines Filter = [Line ID]<=[Outer Bar Length]

Now just drag the [Extra Lines Filter] field onto the Filter shelf, and filter on TRUE and voila!

There is one more step that’s completely optional. The way we set up this data source, we can have up to 50 “Cross” lines for the largest bar. But maybe you want less than that. I like to make my visualizations as dynamic as possible so I can play around with how it looks. Earlier we created a parameter called [Cross Lines]. We can use that parameter to determine how many lines we want to use. We’re just going to create one additional calculated field.

Max Cross Lines Filter = [Line ID]<=[Cross Lines]

Just drag that field onto the Filter shelf, filter on TRUE, and then right click on the pill and choose “Add to Context”. Now you can adjust the number of lines, and in doing so, the spacing between the lines. Here’s what it looks like with 30 lines instead of 50.

If you want to use more than 50 lines, just add some additional rows to the “Lines” densification table.

Final Touches

So now our view is built, but there is one critical piece of information missing from our chart…Row Labels. There are a few different ways you can add these, but I’m going to cover two quick options; Shapes and Labels.

For the viz that I shared earlier, I was publishing it to Tableau Public, which has pretty limited options when it comes to fonts, and I really wanted a “hand-drawn” font. What I ended up doing was creating custom shapes for each of my labels in PowerPoint. If you decide to go this route, one thing you want to make sure that you do is to set the size of each of the text boxes equal. If the text boxes are different sizes when you save them as images, it will look like the text is a different size for each value because Tableau will attempt to “normalize” them.

So first, create your shapes in PowerPoint. Here, I inserted 10 text boxes, typed my movie names, set the alignment to “Right”, and then set the Font to “Caveat”. Then I clicked and dragged to highlight all 10 text boxes, and in the top right corner of PowerPoint, in the Shape Format options, I set the “Width” so that they would all be the same size. regardless of how long the text in each box actually is.

Then I right-clicked on each image, saved them to a folder in my “Shapes” repository. Finally, I created a new sheet using the “Shapes” mark type, positioned them by Rank, and then fixed and reversed the axis so they would align with the bars. Then you can throw these two sheets in a container on your final dashboard and have something like this.

So that’s an option if your data is static and you don’t have a lot of values. This would be nearly impossible to maintain if new values were constantly being introduced, and would be way too much work if there were a lot of values in your data source. In those cases, you may need to go with more traditional labels and be limited to the available fonts. But even that can be a little bit tricky because these are lines, not bars.

For this we need one more calculated field. We only want to label 1 point for each of our bars but we can’t filter out any points. We also can’t use a calculation that results in some null values and some populated values for a given Line Type (because it can inadvertently remove sections of the lines we worked so hard to draw).

Label Name = if [Line Type]=’Outer Lines’ then [Name] END

Now just drag the [Label Name] field to Label and then set the Label options as follows.

You can choose whichever font type and size you prefer, but make sure to set the Alignment to “Top Left’, select “Line Ends” under Marks to Label, and under Options, de-select “Label end of line”. Between the calculated field and these options, only 1 Label will appear per bar, and it will appear to the top left of Point 1 of the “Outer” line (which is the bottom left point in each bar). It should end up looking something like this.

Those are a couple of ways to add Row Labels to your “hand-drawn” bar charts. If you want to add value labels as well you can follow a pretty similar process, but it’s a little trickier. This post is already long enough so I’m not going to go into that, but if you make it this far and want to add value labels, please reach out and I’d be happy to help you. Or you can take the easy way out and do what I did, and just create an image of a “hand-drawn” axis and add it to your dashboard.

Thank you so much for reading, and keep an eye on the blog for more ‘Totally Useless Charts & How to Build Them’!

Categories
How-To's It Depends Tableau Techniques

It Depends: Techniques for Filtering on Multiple Selections with Dashboard Actions in Tableau

Welcome to installment #3 of the “It Depends” blog series. If you’re not familiar with the series, each installment will cover a question where there is no clear definitive answer. We’ll talk through all of the different scenarios and approaches and give our recommendations on what approach to use and when. The question we are tackling this week is “How can dashboard actions be used to filter on multiple selections in a sheet?”. Pretty easy right. Just use a filter action…or a set action…or set controls…or a parameter action. There are clearly a lot of different ways to accomplish this, but which one should you use? And I think you know the answer…It depends!

But before we start…why does this even matter? Why not just throw a quick filter on the dashboard and call it a day? For me, it’s about user experience. When a user sees a mark on a dashboard and they want to dig into it, would they rather a) mouse over to a container filled with filters, find the right filter, click on a drop down, search for the value they want to filter, click that value, and then hit apply, or b) just click on the mark? Don’t get me wrong, quick filters are important, and often times essential, but they can also be a bit clunky, and can hinder performance. So whenever possible, I opt for dashboard actions.

Using dashboard actions to filter from a single mark is pretty easy, but the process gets a little more complicated when you want to be able to select multiple marks. And as developers, we have a choice on whether or not we want to push that burden onto our users. We have to decide what’s more important, ease of use for our users, or ease of setup for ourselves. We’ll also need to take performance into consideration. Those will be the two biggest decision points for which of these 4 methods to implement. And those methods are, as I mentioned above; Filter Actions, Set Actions, Set Controls, and Parameter Actions. There are no restrictions on these four approaches. Any one of them could be used in any scenario, but it’s up to you to weigh the importance of those two decision points and to determine which method gives you the right balance for your specific situation.

The Four Methods

Filter Action – This is the easiest of the four methods to implement and provides users with a better user experience than quick filters, both in terms of interacting with the dashboard and performance. But there are some downsides. First off, in order to select multiple marks, users would either need to hold down the CTRL key while making their selections, or those marks would need to be adjacent so that users could click and drag to select multiple marks together. Not ideal. Also, with Filter Actions, the selections cannot be used in other elements of your dashboard (calculated fields, text boxes, etc), like they can with other methods. And finally, and this one might be a bit personal, you can’t leverage any of the highlighting tips that I discussed in Installment 1 of this series, Techniques for Disabling the Default Highlighting in Tableau. Technically, you could use the “Highlight Technique” but because you can’t use the selected values in a calculated field, there would be no clear way to identify which mark(s) have been selected. Full disclosure, I never use this technique, but I’m going to include it because technically it will accomplish what we’re trying to do.

Set Action – This method provides a somewhat similar user experience to Filter Actions. Better performance than quick filters, but users still need to hold CTRL, or click and drag to select multiple marks. The main benefit of this approach over Filter Actions is it’s flexibility. You can use it to filter some views, and in other views you can compare the selected values to the rest of the population. This type of analysis isn’t possible with Filter Actions. With Filter Actions, your only option is to filter out all of the data that is not selected. With Set Actions, you can segment your data into selected and not selected. You can also use those segments in calculated fields, which is another huge benefit over Filter Actions.

Set Controls – This method provides all of the same benefits of Set Actions, but with one major improvement. Users do not need to hold CTRL or click and drag. They can click on individual marks and add them one by one to their set. This method is a little more difficult to set up than the previous two, but in my opinion, it is 100% worth it. It’s possible that it may be marginally worse for performance, but I have never had performance issues using this method. The only things that I don’t like about this approach are that you can’t easily toggle marks in and out of the set (you can do this with a second set action in the ‘menu’, but it’s a bit clunky), and you can’t leverage the Filter Technique for disabling the default highlighting (we’ll talk more about this later).

Parameter Action – In my opinion this approach provides the best user experience. Users can select marks one by one to add to their “filter”, and can also, depending on how you set it up, toggle marks in and out of that filter. The main downside here is performance. This technique relies on some somewhat complicated string calculations that can really hurt performance when you’re dealing with large or complex data sets. It’s also the most difficult to implement. But when performance isn’t a concern, I love this approach.

Method Comparison

So which method should you use? Let’s take a look at those two decision points that I mentioned earlier, User Experience and Performance.

If neither of these decision points are important then you can ignore this entire post and just use a quick filter. But that’s usually not the case. If Performance is important, but you’re less concerned with User Experience, you can use either a Set Action or a Filter Action (but I would recommend Set Actions over Filter Actions). If User Experience is important and Performance is not a concern, you can use a Parameter Action. And if both User Experience and Performance are important, then Set Controls are the way to go. But as I mentioned earlier, you are not limited to any of these methods in any scenario, and there could be other elements that influence your decision. So let’s do a closer comparison of these methods.

*Flexibility refers to how the selected values can be used in other elements of the dashboard
**Default Highlight Techniques refer to which technique (from Installment 1) can be used with each method

So now we have a pretty good idea which method we should use in any given scenario. Now let’s look at how to implement each of these.

Methods in Practice

For each of these methods we are going to use this ridiculously simple Sales dashboard. We’re going to use the bar chart on the left (Sales by Subcat) to filter the line chart on the right (Sales by Quarter). If you’d like to follow along, you can download the sample workbook here.

Filter Action

As I mentioned earlier, this is the easiest method to set up, but will require your users to either hold CTRL or click and drag to select multiple marks.

  • Go to Dashboard > Actions and click “Add Action”
  • Select “Filter” from the list of options
  • Give your Action a descriptive Name (ex. SubCat Filter)
  • Under “Source Sheets” select the worksheet that will drive the action. In this example it is our bar chart “Sales by SubCat”.
  • Under “Target Sheets” select the worksheet that will be affected by the action. In this example it is our line chart “Sales by Quarter”
  • Under “Run action on” choose “Select”
  • Under “Clearing the selection will” choose “Show All Values”
  • Click “OK”

When you’re finished, your “Add Filter Action” box should look like this

And now when we click on a mark, hold CTRL and click multiple marks, or click and drag to select multiple marks our line chart will be filtered to just the selected value(s). And when we click in the white space on that sheet, or the selected value (when only one mark is selected) the filter will clear and the line chart will revert to show all values

Set Action

Filtering with Set Actions is slightly more involved but still pretty straightforward. For this method we need to create our set, we need to add that set to the Filter Shelf on our line chart, and then we need to add the dashboard action to update that set.

  • Go to the sheet that will be affected by the action. In this case it is our line chart (Sales by Quarter).
  • Create a set for the field that will be used for the filter. In this case it is our [Sub-Category] field.
    • Right click on [Sub-Category]
    • Click on “Create”
    • Click on “Set”
    • Click the “All” option to select all values
    • Click “OK”
  • Add the filter to the sheet that will be affected by the action (Sales by Quarter).
    • Drag the [Sub-Category Set] to the filter shelf on the “Sales by Quarter” worksheet
    • It should default to “Show Members in Set”, but just in case, click on the drop-down on the [Sub-Category Set] pill on the filter shelf and make sure that is the option selected
  • Add the dashboard action to update the [Sub-Category Set]
    • Navigate back to the dashboard
    • Go to Dashboard > Actions and click “Add Action”
    • Select “Change Set Values” from the list of options
    • Give your Action a descriptive Name (ex. SubCat Set Action Filter)
    • Under “Source Sheets” select the worksheet that will drive the action. In this example it is our bar chart “Sales by SubCat”.
    • Under “Target Set” select the set that was used as the filter in the previous steps. In this case it is our [Sub-Category Set]
    • Under “Run action on” choose “Select”
    • Under “Running the action will” choose “Assign values to set”
    • Under “Clearing the selection will” choose “Add all values to set”
    • Click “OK”

When you’re finished your “Add Set Action” box should look like this.

The way this Set Action configuration works is that each time you make a new selection, the contents of the set are being completely overwritten with the newly selected values. That’s what the “Assign values to set” option does. And when you clear the selection, by clicking on white space in the sheet, or the last selected value, the contents of the set are replaced again with all of the values. That’s what the “Add all values to set” option does.

I would recommend one additional step if you’re using this method to override the default highlighting. When using Set Actions you are somewhat limited on what techniques you can use for this, but the “Highlight Technique” works great. You can read about how to use that technique here. Once you’ve added the Highlight Action, just put the [Sub-Category Set] field on color on your “Sales by Subcat” sheet and select the colors you want to display for marks that are selected (in the set) and marks that are not selected (out of the set). When you’re done, your dashboard should look and function like this. Keep in mind that similar to Filter Actions, users will need to hold CTRL and click, or click and drag to select multiple marks.

Set Controls

Setting up our filter with Set Controls is going to be very similar to Set Actions, but with one major difference. The way Set Controls work is that they allow you to select marks one by one and either add them to your set or remove them from your set. This is a great feature, but it makes filtering with them a little tricky.

If we were to start with all of our values in the set, we couldn’t just click on a value in our bar chart to add it to the set, since it’s already there (as well as all of the other values). So we need to start with the set empty and then start adding values when we click on them. But if our set is empty, and we use that set as a filter, as we did in the previous example, then our line chart will be blank until we start adding values. And we don’t want that. We want the line chart to show all of the values, until we start selecting values, and then we want it to just show those values. And we can accomplish this with a calculated field. So first, we’re going to create our set, then we’ll create our calculated field, then we’ll add that field as a filter to our line chart, and then we’ll add the action to update the set.

  • Go to the sheet that will be affected by the action. In this case it is our line chart (Sales by Quarter).
  • Create a set for the field that will be used for the filter . In this case it is our [Sub-Category] field. (skip this step if you followed along with the Set Action example)
    • Right click on [Sub-Category]
    • Click on “Create”
    • Click on “Set”
    • Click the “All” option to select all values
    • Click “OK”
  • Create a calculated field called [SubCat Filter]
    • { FIXED : COUNTD([Sub-Category Set])}=1 OR [Sub-Category Set]
  • Add the filter to the sheet that will be affected by the action (Sales by Quarter).
    • Drag the [SubCat Filter] field to the filter shelf on the “Sales by Quarter” worksheet
    • Filter on “True”
  • Add the dashboard action to update the [Sub-Category Set]
    • Navigate back to the dashboard
    • Go to Dashboard > Actions and click “Add Action”
    • Select “Change Set Values” from the list of options
    • Give your Action a descriptive Name (ex. SubCat Set Control Filter)
    • Under “Source Sheets” select the worksheet that will drive the action. In this example it is our bar chart “Sales by SubCat”.
    • Under “Target Set” select the set that was used as the filter in the previous steps. In this case it is our [Sub-Category Set]
    • Under “Run action on” choose “Select”
    • Under “Running the action will” choose “Add values to set”
    • Under “Clearing the selection will” choose “Remove all values from set”
    • Click “OK”

When you’re finished your “Add Set Action” box should look like this.

So there are a couple of things we should cover here, starting with the calculated field. Here it is again.

{ FIXED : COUNTD([Sub-Category Set])}=1 OR [Sub-Category Set]

Sets only have two possible values; IN or OUT. The set may contain hundreds or even thousands of values from the source field, but the sets themselves can only have these two values. So if we use a FIXED Level of Detail expression and count the distinct values, the result will be either 1 or 2. If the set is empty, the value for every record will be OUT, so the result of the LOD will be 1. Similarly, if the set contains all values, the value for every record will be IN, so the result of the LOD will still be 1. But if some values are in the set (IN) and other values are not in the set (OUT), then the result of the LOD will be 2 (IN and OUT).

So the first part of this calculated field ( FIXED : COUNTD([Sub-Category Set])}=1) will be true for all records when the set is empty, or if it contains all values. The second part of this calculated field (OR [Sub-Category Set]) will only be true for records in the set. So when we start with an empty set, the overall result of this calculated field will be True for every record, so everything will be included in our line chart. As soon as we add a value to our set, the first part becomes false for every record, but the second part becomes true for the values in our set. Because we are using an OR operator, the overall result, once we click on some values, will be true for the selected values and false for the rest of the values.

Next, let’s look at the Set Control options. We are starting with an empty set. Each time we click on a new mark, that value will be added to our set. That’s what the “Add values to set” option does. Unlike the “Assign values to set”, it does not override the set, it just adds new values to the existing ones. And then when we click on some white space in the sheet, or the last selected value, the set will go back to being empty. That’s what the “Remove all values from set” option does.

And just like in the previous example, I would recommend using the “Highlight Technique” covered here, and then adding the [SubCat Filter] field to color on the bar chart (Sales by SubCat).

And now your dashboard should look and function like this. Notice that you no longer need to CTRL click, or click and drag, to select multiple values. Nice!

Parameter Action

This method is by far the most complex, but if done correctly, it provides a really smooth user experience. The thing that I like most about this approach is that you can set it up so that you can “toggle” values in and out of the filter. There are a few extra steps in this approach, and some somewhat complex calculations. We need to create our parameter, create a calculated field that will be passed to that parameter, add that field to Detail on our bar chart, create a calculated field for our filter, add that filter to our line chart, and add the dashboard action that will update the parameter.

  • Create a string parameter called [SubCat Select] and set the “Current value” to the pipe character “|”
  • Create a calculated field called [SubCat String]
    • IF CONTAINS([SubCat Select], ‘|’ + [Sub-Category] + ‘|’) THEN REPLACE([SubCat Select],’|’ + [Sub-Category] + ‘|’,’|’)
    • ELSE [SubCat Select] + [Sub-Category] + ‘|’
    • END
  • Go to the sheet that will drive the parameter action and drag the [SubCat String] field to Detail. In this example, that is the “Sales by SubCat” sheet
  • Create a calculated field called [SubCat String Filter]
    • [SubCat Select]=’|’ OR CONTAINS([SubCat Select],’|’+[Sub-Category]+’|’)
  • Add the filter to the sheet that will be affected by the action (Sales by Quarter).
    • Drag the [SubCat String Filter] field to the filter shelf on the “Sales by Quarter” worksheet
    • Filter on “True”
  • Add the dashboard action to update the [Sub-Category Set]
    • Navigate back to the dashboard
    • Go to Dashboard > Actions and click “Add Action”
    • Select “Change Parameter” from the list of options
    • Give your Action a descriptive Name (ex. SubCat Parameter Filter)
    • Under “Source Sheets” select the worksheet that will drive the action. In this example it is our bar chart “Sales by SubCat”.
    • Under “Target Parameter” select the parameter that was set up in the previous steps. In this example it is [SubCat Select]
    • Under “Source Field” select the [SubCat String] Field
    • Under “Aggregation”, leave set to “None”
    • Under “Run action on” choose “Select”
    • Under “Clearing the selection will” choose “Keep Current Value”
    • Click “OK”

When you’re finished, your “Add Parameter Action” box should look like this

Alright, let’s talk through some of those calculations, starting with the [SubCat String] Field. Basically, what this calculation is doing is building and modifying a concatenated string of values. Here’s that calculation again.

IF CONTAINS([SubCat Select],’|’ + [Sub-Category] + ‘|’) THEN REPLACE([SubCat Select],’|’ + [Sub-Category] + ‘|’,’|’)
ELSE [SubCat Select] + [Sub-Category] + ‘|’
END

We set up our parameter to have a default value of just the pipe character (“|”). I’ll explain the use of the pipes a little later on. The first line of the calculation looks to see if the selected value is already contained in our string. So it’s searching our entire concatenated string for the pipe + selected value + pipe (ex. |phones|). If it finds that value in our string, it will replace it with just a pipe. So for our example, if our parameter value was |phones|binders|storage| and we click on phones, it will replace “|phones|” with a pipe, leaving “|binders|storage|”

The second line of this calculation will add the selected value. The calc has already tested to see if it’s there in the previous step, and if it’s not, this line will add it along with a pipe. Now let’s look at our parameter action…in action.

Take a look at the parameter at the top. As we click on each Sub-Category, its added to our string. Now look what happens when we click on those selected marks again.

As we click on each previously selected mark, that mark is removed from our string, until we’re left with just our starting value, the pipe.

The reason I use pipes on both sides, instead of just a comma separated list is to avoid situations where one potential value could be equal to a part of another value (ex. Springfield and West Springfield). The pipes ensure we are matching exact values in the next step, our filter calculation. Here’s that filter calculation again.

[SubCat Select]=’|’ OR CONTAINS([SubCat Select],’|’+[Sub-Category]+’|’)

This is pretty similar to the filter that we created in our Set Controls example. The first part is checking to see if our concatenated string is “empty”, meaning nothing has been selected yet and it’s just a pipe. If that’s the case, the result of this calculated field will be true for every record. The second part of the calculated field is checking the [Sub-Category] field in each record and seeing if that value is contained within our parameter string (along with the starting and ending pipe). If it is, the result of this calculation will be True for those records and False for all of the other records.

When using this method, I would highly, highly, highly recommend using the “Filter Action” technique for disabling the default highlighting. You can find it here. This technique not only masks the selection, but it also automatically de-selects a mark after you click on it. That is really important when using this method. Once you add that Filter Action, just add the [SubCat String Filter] field to color, and you should be good to go. Here’s what it looks like in action.

And there you have it. Four different methods for filtering your dashboards on multiple selections. As I mentioned before, some of these are much more complex than others, but they provide a much better user experience. In my opinion, it’s worth it to put in the extra time and effort to save your users frustration later on. I hope you enjoyed this post, and keep an eye out for more installments of “It Depends”.

Categories
How-To's Tableau Techniques Totally Useless Charts

Totally Useless Charts & How to Build Them – Lotus Flowers

Welcome to our new series, Totally Useless Charts & How to Build Them. In each installment of this series we’ll look at one very custom chart, something with almost no real use cases, and we’ll walk through, step by step, how to build it. The purpose of this series isn’t necessarily to teach you how to build these specific useless charts, it’s more about talking through the techniques, the approach, and the thought process behind each chart. Our hope is that seeing how we went about building these will help you with your own custom charts. But if you do somehow find a great use case for one of these charts, by all means, please download the workbook and use it as your own.

In this first installment we’re going to learn how to build Lotus Flowers in Tableau. It’s not a requirement, but it may be a good idea to review Part 1 and Part 2 of the Fun With Curves Series before proceeding. To follow along, you can download the workbook here, and the data here.

Lotus Flower

First, let’s take a look at what we’re trying to build. Below is a lotus flower with 10 petals, which means we have a total of 11 polygons; 1 circle and 10 petals. The circle is fairly easy to build using the techniques in Part 1 mentioned above. The petals are a little more complicated. But first thing’s first…we need some data.

Building Your Data Source

Let’s start with our data. For this example we’re going to build 12 lotus flowers and we’re going to use the value from our data source to size the flowers appropriately. We’ll start with the tab titled ‘Source Data’.

Next, we’re going to do some densification to get the number of polygons needed for each of the flowers. Below I have 1 record for the Circle and 24 records for the petals. We’re going to build this in a way that will let you choose how many petals you want to display (up to 24). This data can be found in the ‘Polygons’ tab in the sample data.

Now we’re going to join these two sources together using a join calculation (value of 1 on each side). The result will be 25 records for each of our 12 ‘Base’ records.

Next, we need to do a little more densification, but this time it’s a little trickier. For our circle, we want at least 50 points for a relatively smooth curve. For our petals, we actually need to draw 2 lines for each petal, one for the left side of the petal (Min) and one for the right side of the petal (Max), and then join those together. Pretty confusing right? We’ll talk about this in a lot more detail. This table is a little too large to include a screenshot, but take a look at the ‘Densification’ tab in the sample data.

For our circles, we have 50 records. We have two numerical fields, [Points] and [Order], that both run from 1 to 50 and a [Type] field to identify that these points are for our circles. For our petals, we have 100 records. We still have the same two numerical fields, but the values are a little different. We have an [Order] field that runs from 1 to 100, and a [Points] field that runs from 1 to 50 and then back down from 50 to 1. We also have a [Side] field with values of Min or Max. The Min records will be used to draw the left side of our petals. The Max records will be used to draw the right side of our petals. And then we have a [Type] field to identify that these records are for our petals. Now we just need to join this table to our data source on the [Type] Field.

Building Your Circles

If you have read through Part 1 of the Fun With Curves series, then you may remember that in order to draw a circle in Tableau, we only need 2 inputs; the distance of each point from the center of the circle (the radius), and the position of each point around the circles (represented as a percentage).

Let’s start with the first input, the radius. We are going to size our circles based on the Value field in the Source Data. We want the area of our circles to represent the value in the data. So we have the area of each circle, we just need to use those values to calculate the radius of each circle. We can do this with the simple calculation below.

Radius = SQRT([Value]/PI())

Next, we need to calculate the position of each point around the circle. I’m not going to go into too much detail on this, but you can read more about it in the post mentioned above. To calculate this, we need the maximum number of points for our Circles (50), and we need the [Points] field (values 1 thru 50). For the max point calculation I am going to use an LOD because the max number of points for our circles, may not always align with the max number of points in our data source (but in this case it does).

Max_Point = {FIXED [PolygonType] : MAX([Points])}

Circle_Position = ([Points]-1)/([Max_Point]-1)

Next, we just need to plug the [Radius] and the [Circle_Position] values into our X and Y formulas for plotting points around a circle.

Circle_X = [Radius]* SIN(2*PI() * [Circle_Position])

Circle_Y = [Radius]* COS(2*PI() * [Circle_Position])

Now, let’s draw our circles

  • Right click on [Circle_X] and drag it to columns. When prompted, choose [Circle_X] without any aggregation
  • Right click on [Circle_Y] and drag it to rows. When prompted, choose [Circle_Y] without any aggregation
  • Right click on [Base_ID], change it to a Dimension, and drag it to Detail
  • Right click on [Order], change it to a Dimension, and drag it to Path
  • Drag [Type] to Filter Shelf and filter to ‘Circle’
  • Change the Mark Type to Polygon

Now you should have something that looks like this.

Although it looks like one big circle, we actually have all 12 circles in this view. They’re just stacked on top of each other. So next we need to space these out a little bit. There are a lot of different techniques to do this, but here’s one I like to use to create Trellis Charts. This technique works great when you have a sequential ID field, which we do (Base_ID).

First, we’re going to create a numeric parameter that will allow us to choose the number of columns we want to create. We’ll call the parameter [Column Count] and set the value to 3. Next, we’re goin to use the [Base_ID] field to break our circles into columns and rows, starting with row.

Row = CEILING([Base ID]/[Column Count])

Column = [Base ID]-(([Row]-1)*[Column Count])

Now right click on both of these fields, change them to Dimensions, and then drag them to the appropriate shelf (Row to Rows, Column to Columns). The result should look something like this.

Building Your Petals

Alright, so this part is a little more complicated. I’m going to start by reviewing the basics of how you build these shapes, but I’m going to skim over the calculations since those will change significantly once we try to build these petals around our circle. No need to follow along with the workbook during this section.

So here are the basics. Let’s start by drawing a Bezier Curve with 4 control points. Our line is going to start at 0,0 and end at 5,10. Wow, this is easy, we already have the coordinates for 2 of the points!

Let’s take a look at our inputs. Our line will have a height of 10 and a width of 5. I’ve also built 2 parameters that we’ll use to calculate the 2nd and 3rd set of points. You can experiment with different values here, but these seem to work pretty well. We need a total of 8 values (4 sets of X and Y).

  • Point 1 will be the start of the line. In this case, it’s 0,0
  • Point 2 will appear on the same X axis as Point 1, but will be somewhere between the start and end of the line on the Y axis. I like to place it two thirds of the way, or .67 (the value in the P2 Parameter Input above). So the coordinates for Point 2 will be 0 and 6.7 (P2 Parameter x Height of the line)
  • Point 3 will appear on the same X axis as Point 4, and will appear somewhere between the start and end of the line on the Y axis (similar to P2). I like to place it halfway, or .5 (the value in the P3 Parameter Input above). So the coordinates for Point 3 will be 5 and 5 (P3 Parameter x Height of the line).
  • Point 4 will be the end of the line. In this case, it’s 5,10

If you were to plot these 4 points, you would have a jagged line like you see in the image above. But look what happens when we plug those values into our Bezier calculations

X = (1-[T])^3*[P1_X] + 3*(1-[T])^2*[T]*[P2_X] + 3*(1-[T])*[T]^2*[P3_X] + [T]^3*[P4_X]

Y = (1-[T])^3*[P1_Y] + 3*(1-[T])^2*[T]*[P2_Y] + 3*(1-[T])*[T]^2*[P3_Y] + [T]^3*[P4_Y]

Alright, we are halfway there! Kind of. So now we have a line that will create 1/2 of one of our petals. But in order to turn this into a petal shaped polygon, we need another line that’s a mirror image of this one.

This is where the Min and Max records come in. We need to calculate our 4 sets of coordinates for both sides. Luckily, most of the values are actually the same. P3 and P4 are going to be identical for both lines. And the Y values for P1 and P2 are the same. The only differences are the X values for P1 and P2. And to calculate those we just add the width of the whole petal (width x2) to our starting point. And if we were to plug these coordinates into the same calculations, we have this.

Now this is where the [Order] field comes into play. To make this one single polygon instead of two separate lines, we can use the [Order] field on Path and change the Mark Type to polygon

On the Left (Min) side, we have points 1 thru 50, running from the bottom left up to the top middle. On the right (Max) side, we have points 1 thru 50 running from the bottom right up to the top middle. But then we have the [Order] field (on label in the image above). This field runs from the bottom left to the top middle to the bottom right, in one continuous flow, from value 1 to 100. This is what makes it a single continuous polygon.

Ok, so that’s how we would build 1 single petal shaped polygon, perfectly positioned facing directly upward. But that’s not what we’re trying to do. We’re trying to build a dynamic number of petals, evenly spread around a circle and facing outward in the appropriate directions. So let’s do that.

Building Your Petals (for real this time)

We’re going to use a similar approach to what was described above, but everything needs to be plotted around a circle. So any calculations we use to determine our 4 points are going to have to run through those X and Y calculations for plotting points around a circle. That means, for all of our points, P1 thru P4 for both sides, we need to calculate two things; the distance from the center of the circle, and the position around the circle. But before we calculate those specific points there are a few other calculations that we’ll need. We also need a parameter, called [Petal Count] that will allow us to select how many petals we want. This should be a numeric parameter, and let’s set the value to 10 (I recommend using a range, allowing values from 4 to 24)

T = ([Points]-1)/([Max_Point]-1) – this is the same as the [Position] calc used earlier. It’s used to evenly space points between 0% and 100%

Petal_Count_Filter = [Polygon ID]<=[Petal Count] – this will be used as a filter to limit the number of petals displayed to what is selected in the [Petal Count ] parameter

Petal_Width = 1/[Petal Count] – this calculates the total position around the circle that will be occupied by each petal. For example, if there were 10 petals, each one would occupy 1/10 of the space around the circle, or .10

Petal_Side_Position = IF [Side]=’MIN’ THEN ([Polygon ID]-1)*[Petal_Width] ELSE [Polygon ID]*[Petal_Width]
END
– this calculates the position of the start of each Min line and Max line. If there were 10 petals, the Min side of the 2nd petal would be at position .1 or (2-1)*.1, and the Max side of the petal would be at position .2, or 2*.1. The Min value will share the same position as the Max value of the previous petal. The max value will share the same position as the Min value of the next petal

Petal_Middle_Position = ([Polygon ID]/[Petal Count]) – ([Petal_Width]/2) – this calculates the position of the center of each petal. If there were 10 petals, the center of petal 3 would be at .25, or (3/10) – (.1/2). This is also halfway between the position of the Min line and the Max line.

Alright, now we can calculate all of our coordinates. Let’s start with P1. For the first input, we want this point to start right at the edge of our circle. So the distance from the center is going to be equal to the radius of the inner circle. So the first input is just [Radius]. For the second input, we’ll use the the [Petal_Side_Position] we calculated above.

P1_X = [Radius]* SIN(2*PI() * [Petal_Side_Position])

P1_Y = [Radius]* COS(2*PI() * [Petal_Side_Position])

If we were to plot these points for 12 petals, we would end up with 24 points, but it would appear that we only 12 because each is overlapping with another point. But this gives us the outside edges of each of our petals

Now onto P2. This one is a little more complicated. We’re going to use P1 as a starting point for this calculation, instead of the center of the inner circle. Now we need to calculate the distance from P1 where we want our next point to appear. First, we need to determine the length of the entire petal. I like to use a parameter for this so I can dynamically adjust the look of the flowers. So let’s create a parameter called [Petal_Length_Ratio]. This is going to be a number relative to the radius, so a ratio of 1 would set the length of the petal equal to the radius of the circle. A value of .8 would set the length of the petal equal to 80% of the radius, and so on. I usually go with a value somewhere between .5 and 1. We’ll use this along with the radius, so that the petals of each flower are sized appropriately based on the size of their inner circle. Next, we need to position this point somewhere between the start of the line and the end of the line. As I mentioned earlier, I like to place it two thirds of the way (P2_Parameter from the previous section). So the first input, the distance from P1, is going to be the radius x the length ratio x the P2 parameter. For the second input, we’re going to use the [Petal_Middle_Position] because we want this side of the line to follow the same path as the line with P3 and P4. If we were to use the [Petal_Side_Position] field, we would end up with really wide, strange looking petals. This will probably make more sense a little further along. For now, let’s plug those values into our X and Y calcs.

P2_X = [P1_X] + (([Radius] * [Petal_Length_Ratio] * [P2_Parameter]))* SIN(2*PI() * [Petal_Middle_Position])

P2_Y = [P1_Y] + (([Radius] * [Petal_Length_Ratio] * [P2_Parameter]))* COS(2*PI() * [Petal_Middle_Position])

P3 is a little more straight forward. For the first input, we’re going to calculate the distance from the center of the inner circle. And then we’ll use a similar approach to what we did for P2. The first input will be the radius + (the radius x the length ratio x the P3 parameter). As I mentioned in the earlier section, I like to set this parameter to .5. And once again, we’re going to use the [Petal_Middle_Position] field for the second input.

P3_X = ([Radius]+([Radius] *[Petal_Length_Ratio] * [P3_Parameter]))* SIN(2*PI() * [Petal_Middle_Position])

P3_Y = ([Radius]+([Radius] *[Petal_Length_Ratio] * [P3_Parameter]))* COS(2*PI() * [Petal_Middle_Position])

P4 is almost identical to P3, except we don’t need the length ratio. We want this point to appear at the end of the line. So we can just remove that from the calc.

P4_X = ([Radius]+([Radius] * [Petal_Length_Ratio])) SIN(2*PI() * [Petal_Middle_Position])

P4_Y = ([Radius]+([Radius] * [Petal_Length_Ratio])) COS(2*PI() * [Petal_Middle_Position])

We’re almost there! If we were to plot these points for the first petal in our lotus flower, it would look like this. It looks very similar to what we reviewed in the previous section, but with one very important difference…everything is at an angle…which is what we wanted.

All that’s left to do is to plug all of these points in our Bezier calcs and then build our polygons!

Petal_X = (1-[T])^3*[P1_X] + 3*(1-[T])^2*[T]*[P2_X] + 3*(1-[T])*[T]^2*[P3_X] + [T]^3*[P4_X]

Petal_Y = (1-[T])^3*[P1_Y] + 3*(1-[T])^2*[T]*[P2_Y] + 3*(1-[T])*[T]^2*[P3_Y] + [T]^3*[P4_Y]

Now the polygons. Let’s build this as a Trellis chart, just like we did with the Circles. So drag [Row] on to Rows and [Column] onto Columns. And then;

  • Right click on [Petal_X] and drag to Columns. When prompted, select [Petal_X] without aggregation
  • Right click on [Petal_Y] and drag to Rows. When prompted, select [Petal_Y] without aggregation
  • Drag [Type] to Filter Shelf and filter to ‘Petal’
  • Drag [Petal_Count_Filter] to Filter Shelf and filter to TRUE. Right click and ‘Add to Context’
  • Drag [Polygon_ID] to Detail
  • Drag [Order] to Path
  • Change Mark Type to Polygon

We’re so close! Your sheet should look like this

The only thing left to do is to combine the Circle polygons with the Petal Polygons. We have separate data for them, all we need to do is get them on the same sheet. So we’ll create two more simple calcs to bring it all together.

Final_X = IF [Type]=’Circle’ THEN [Circle_X] ELSE [Petal_X] END

Final_Y = IF [Type]=’Circle’ THEN [Circle_Y] ELSE [Petal_Y] END

Now just replace [Petal_X] and [Petal_Y] with [Final_X] and [Final_Y] and drag [Type] from the filter shelf on to Color and you should have your lotus flowers!

The Final Touches

The hard part is done, now to make it look pretty. Play around with some of the parameters until you get the look that you like. Adjust the [Petal Count], the [Column_Count], the [Petal_Length_Ratio], and even the [P2_Parameter] and [P3_Parameter] if you wanna get crazy.

Next, throw some color on there. You could make the color meaningful to encode some data, or you could do what I just did and color it randomly. I used the calc below and then just assigned one of the color palettes I have saved.

Color = [Type] + STR([Base ID])

And that’s it! If you made it this far, please reach out and let me know what you thought, and what you came up with. Thank you so much for reading, and keep an eye on the blog for more ‘Totally Useless Charts & How to Build Them’

Categories
Design Figma It Depends

It Depends: Using design tools in your dashboard design process

You may have heard people talk about Figma or Illustrator, or maybe you’ve heard people talking about wireframes or prototypes. Perhaps you’ve seen dashboards with custom backgrounds. Some questions seem to come up often: What do you use Figma for? What are wireframes? Do I need prototypes? Should I use background images in my dashboards? Are these tools just something to use for flashy dashboards for Tableau Public? Why wouldn’t you just do your mockup in Tableau?

These are all really good questions to be asking, especially if you haven’t used these tools in your work before. In this installment of the “It Depends” series, I’ll unpack how and when I use design tools in my dashboard development process.

Just a quick note to say, I might talk about Figma a lot here, but this post isn’t about Figma specifically. There are other tools that you can use to accomplish similar things to varying degrees. Plenty of people use PowerPoint, Google Slides, and Adobe Illustrator just to name a few. Autumn Battani hosted a series on her YouTube channel that demonstrates this very well (link). If you want to see how different tools can accomplish the same task, give them a watch!

Why would I use design tools?

In my mind, it boils down to two reasons to use a design tool like Figma in your process:

  1. Create design components such as icons, buttons, overlays, and background layouts, or
  2. Create wireframes, mockups, and prototypes

So, let’s get into when and why you might use these…

Design Components

For business dashboards, it’s usually best to try to keep external design components to a minimum, but when used effectively, they can improve your dashboard’s appearance and the user’s experience.

Icons and Buttons

Icons can be a nice way to draw the user’s eye or convey information in a small space. Custom buttons and icons can add polish to your dashboard’s interactivity. But, they can also be confusing to the user if they’re not well-chosen. So, what are some considerations that can help ensure your icons are well-chosen?

Is the meaning well understood?

While there are no completely universal icons, stick to icons that commonly have the same meaning across various sites, applications, operating systems, and regions.

For example, nearly every operating system you use will use some variation of an envelope to mean “mail”. They might look different, but we can usually figure out what they mean.

iOS mail icon, Microsoft mail icon, and Google mail icon
iOS mail icon, Microsoft mail icon, and Google mail icon

Are they simple and easy to recognize?

Avoid icons with a lot of details and icons that are overly stylized. Look for a happy medium. Flat, lower detail icons are generally going to be easier to recognize and interpret. Once you’ve chosen an icon style, use that style for all icons.

In this example below, the first icon is a very detailed, colorful mail icon, the second is a stylized envelope, and the third is a simple outline of an envelope. The third icon is going to be recognizable for the most people.

colored mail icon, stylized mail icon, simple mail icon
detailed, stylized, and minimal icon (From Icons8.com)

Is there a text label or will you include alt-text and tooltips?

Text labels and alt-text are not only important for accessibility, they can help bridge any gaps in understanding and clarity.

Does it improve the clarity or readability of the visualization?

Avoid icons that distract or are unnecessary. Using icons strategically and sparingly will ensure they draw the eye to the most important areas and reduce visual clutter.

This quote from the Nielsen Norman Group is a good way to think about using icons in your designs:

“Use the 5-second rule: if it takes you more than 5 seconds to think of an appropriate icon for something, it is unlikely that an icon can effectively communicate that meaning.”

Nielsen Norman Group

Some places to use icons:

  • Information:
    • Including an information icon can be a great way to use a small amount of real estate and a recognizable symbol to give users supplemental information about a dashboard without cluttering the dashboard
  • Filters:
    • Hiding infrequently used filters in a collapsible container can reduce clutter on the dashboard while still providing what is needed
  • Show/Hide alternate or detailed views:
    • An icon to allow the user to switch to an alternate view such as a different chart type or a detailed crosstab view, or to show a more detailed view on demand

Background Layouts

Background designs can help create a polished, slick, dashboard. Something you might use for marketing collateral, infographics, and executive or customer-facing dashboards. A nicely designed background can elevate a visualization but they do come with trade-offs.

Does it improve the visual flow of information?

Backgrounds can be used to add visual hierarchy, segmentation, and to orient or guide the user.

Does it distract from the information being presented?

When backgrounds are busy or crowded, they take away from rather than elevate the data being visualized.

Does it affect the maintainability of the dashboard?

Custom background images need to be maintained when a dashboard is changed, so they should be included thoughtfully.

Does it adhere to your company’s branding and marketing guidance?

Background images that are cohesive with other areas will feel more familiar to your users which can make your solution feel more friendly

Does it have text?

Whenever possible, use the text in Tableau as it will be easier to update and maintain, and is available to screen readers. If you need to put the text in the background image for custom fonts, you can use alt-text or hidden text within Tableau.

Find Inspiration

If you’re looking for a place to start with designing layouts, I suggest checking out Chantilly Jaggernauth’s blog series, “Design Secrets for a Non-Designer“, and conference presentation of the same name.

Look at Tableau Public, websites you find easy to use, product packaging. Take note of what works well (and what doesn’t).

This Viz of the Day by Nicole Klassen is a great example of using images that set the theme, elevate the visualizations, and create visual flow and hierarchy.

Of course, it’s not just the data-art and infographic style dashboards that can benefit from this. If you peruse Mark Bradbourne‘s community project #RWFD on Tableau Public, you’ll see plenty of examples using the same concepts to improve business dashboards. Don’t underestimate the impact of good design on usability and perception… It matters.

*Tip: When you use background layouts, you usually have to use floating objects— Floating a large container and tiling your other objects within that container can make it easier to maintain down the line #TeamFloaTiled

Overlays

Overlays can be used to provide instructions to users at the point where they need them. They provide a nice user experience, allow users to answer their own questions, and can save a lot of time in training and ad hoc questions.

Example overlay

Can instructions be embedded in the visualization headings or tooltips effectively?

Overlays are fantastic for giving a brief training overview to users, but they are not usually necessary. Instructions are usually most helpful if 1) the user knows they exist and 2) the information is accessible where it will be needed.

Does the overlay improve clarity, and reduce the need for the user to ask questions?

Overlays should help the user help themselves. If the user still needs training or hands-on help, then it might not be the right solution, or it might need to be changed to help improve the clarity. Sometimes the users just need to be reminded of how to find the information.

Is your dashboard too complex?

Sometimes dashboards need to be complex or they have a lot of hidden interactivity, and there’s nothing wrong with that. However, if you feel like you need to provide instructions it’s always a good idea to step back and consider if the solution is more complex than necessary, or if you can make the design more intuitive. Sometimes complexity isn’t a bad thing, but it’s always worth asking the question of yourself.

Will it be maintained?

Similar to background layouts, overlays will need to be changed whenever the dashboard is changed. Make sure there is enough value in adding an overlay, and that if needed, it will be maintained going forward.

Wireframes, Mockups, and Prototypes

Wireframes, mock-ups, and prototypes are a staple of UX design, and for a good reason. They help articulate the requirements in a way that feels more tangible, they force us to ask questions that will inevitably arise during the development process, and they help solidify the flow and structure. In dashboard design, they can get early stakeholder buy-in, ownership, and feedback. They also help us get clearer requirements before investing in data engineering and dashboard development (and having to rework things later — Tina Covelli has a great post on this subject here). You can talk conceptually about what they need to see, how it needs to work, and the look and feel earlier so it can save time on big projects. I’m a big fan of this process.

So, what’s the difference between wireframes, mockups, and prototypes, and when might you use them?

Wireframes

Wireframes are rough sketches of the layout and components. They can be very low fidelity — think whiteboard drawings of a dashboard. These are great very early on in your process.

Hand drawn wireframe

They can also be a slightly higher fidelity wireframe that starts to show what the dashboard components will be. These are the bones of a dashboard or interface, but can help articulate the dashboard design and move forward the requirements discussion.

Digital wireframe

Even if your stakeholders never see the wireframe, sketching out what your dashboard and thinking about what the layout, hierarchy, interactivity will look like helps organize your thoughts before you get too far or get locked in on a specific idea.

There’s really no reason not to start any project with a wireframe of some sort. This is a tool for the beginning of your process, but once you’ve moved on to mockups or design there’s no reason to do a wireframe unless a complete teardown and rebuild are needed.

Mockups

Mockups are a graphic rendering of what the dashboard might look like. These are high (or at least higher) fidelity designs that allow the user to see what the final product might look like. Exactly how high-fidelity to make the mockups will depend on the project and level of effort you want to invest. You don’t want to spend more time on this process than you would to just do it in Tableau.

Mockup

I think it’s worth noting here: the mockup should be done by the Tableau developer or someone who is very familiar with Tableau functionality. Otherwise, you run the risk that the mockup shows functionality that isn’t going to work well or appearances that aren’t accurate.

If a lot of data prep is required or you are working on a time or resource intensive project, a good mockup is worth its weight in gold. If you jump right into Tableau and find out that it’s more complicated than you initially thought, it’s not too late to pivot and come up with mockups.

Mockups can save you quite a bit of time in the development process. I will use mockups to think about the right data structure and level of detail, and think about how metrics will be calculated or what fields will be needed. And, if your users see a preview of the result and have an opportunity to get involved in feedback early, you are less likely to end up delivering a project that dies on the vine.

Prototypes

As soon as you need to demonstrate interactivity, prototypes come into play. These can be low-fi or high-fi but are useful whenever there is a lot of interactivity to demonstrate. To build interactivity, you’re going to need a prototyping tool. You can get creative and mark up your wireframes and mockups with arrows and comments to show how a user will interact, but prototypes make it feel more real.

The goal of prototypes isn’t to fully replicate the dashboard. A sampling of the interactivity can be included for a demonstration to better convey the idea without spending a lot of time.

You may not need prototypes on many projects, but similar to mockups, if you’re working on a large, complicated project where the stakeholders and users won’t get their hands on a fully functional product for some time, a prototype can be very helpful.

Some things to consider:

  • Is there interactivity that can’t be demonstrated by describing it?
  • Are your users unfamiliar with the types of interaction?
  • Is the user journey complex or multi-stepped?
  • How much functionality needs to be demonstrated?

To sum it up

I believe that involving your stakeholders and user representatives early in the process yields better requirements and a sense of ownership and buy-in. Your stakeholders and users are more likely to engage with, adopt, and encourage the adoption of your solution if they feel ownership.

Knowing that time isn’t an infinite resource, these steps can also take time away from other aspects of the solution or extend the timeline. Sometimes mocking up or iterating right in Tableau will be faster and produce the same result. If you start in the tool, presenting rough versions and getting feedback early is still valuable for the same reasons. Consider if these steps are taking more time than the build itself, or when they add a step that’s not needed to clarify or establish the end goal.

Bonus: Diagrams

Most design tools can also be used to create diagrams. While diagrams aren’t “dashboard design” per se, they are often an important part of documenting or describing a full data solution. What kinds of diagrams might you use in your data solution process?

  • Relationships
    • The good old entity relationship diagram, whether it is a detailed version used for data engineering, or an abstracted version to present to stakeholders
  • User journeys
    • Map out the ways a user can enter the solution, and how they progress through and interact
  • Process flows
    • Flow charts… whether it’s mapping out the process that creates the data, the process for how the solution will be used, or the steps in the data transformation process

Thanks for reading!

Categories
How-To's Tableau Techniques

It Depends: Techniques for Disabling the Default Highlighting in Tableau

The thing that I love most about Tableau is the incredible flexibility. No matter what you are trying to do, there is a way to do it. And more often than not, there are actually several ways to do it. That’s where this series comes in. There are so many incredible hacks and techniques floating out there in the Tableau Universe, it can be difficult to figure out which ones to use and when. In each installment of this series we’ll be focusing on one specific ‘question’ and discuss the pros, cons, and use cases of various techniques. And our first question of the series is… “How do I turn off the default highlighting in Tableau when I click on a mark?”. And the answer is, of course, “It Depends”.

First off, what are we talking about when we say ‘default highlighting’? As I’m sure you have noticed, when you click on a mark in Tableau, something happens to the mark you selected, and to all of the other marks in your view. When you click on a text mark, you get a blue box on the selected mark and all of the other marks fade. When you click on any other type of mark, that mark retains it’s formatting (sometimes with an extra black box around it) and the rest of the marks fade. And then everything in that view goes back to normal when you click on something else.

This behavior makes sense. When a mark is selected, you should know which mark that is. But the result, in my opinion, does not look great. It would be really nice if we could control what the selected and non-selected marks look like.

This post is going to focus on three techniques that will allow you to do just that. We’ll call them the ‘Highlight Technique’, the ‘Filter Technique’, and the ‘Transparent Technique’. First, let’s talk a little bit about each of these techniques, and then we’ll walk through how to apply them. If you’re familiar with the techniques and are just looking for a reminder on how to do one of them, feel free to skip ahead.

The Highlight Technique – This technique leverages a highlight action and essentially highlights every mark when any mark is selected. What I love about this technique is that it’s very simple to set up, and it can be applied to multiple worksheets. With a single action, you can ‘turn off’ highlighting for your entire dashboard…as long as your dashboard doesn’t contain a specific mark type. One of the major drawbacks of this approach is that it does not work with text marks. Instead of getting rid of the blue boxes on your BANs, this technique will turn them yellow. Another drawback of this approach is that it doesn’t actually de-select the mark, it just masks the selection. You can still see a black border around the selected mark, and if you have something that could be clicked on multiple times in a row (like a scroll button), it makes for a clunky user experience. Users would have to click the mark three times to run the action twice (once to run the action, a second time to de-select the mark, and a third time to run the action again). And one last drawback is if you have the opacity turned down on a mark, when you click on it, the mark will show at full opacity.

The Filter Technique – This technique leverages a filter action, and to be completely honest, I’m not entirely sure how it works. But it works great! I was first introduced to this technique by Yuri Fal, during a Twitter discussion on this exact topic, and several others have written about it since. What I love about this technique is that it actually de-selects the mark after you click on it, unlike the Highlight Technique. It also works on any chart type. The only downsides I have found with this approach are that it’s a little tricky to set up, you have to create a separate action for every worksheet on your dashboard, and it does not work well in conjunction with some other actions, mainly other filter actions and set controls. The issue with using this technique with other actions is that you cannot leverage the ‘Clearing the selection will…’ options. So basically there is no option to undo your action. This is a major drawback if you’re trying to use set controls, or another filter action, but not so much with parameter actions since you can replicate that ‘Clearing’ function with a calculated field.

The Transparent Technique – I haven’t used this technique much in the past (mainly because I learned the other techniques first), but it’s definitely something I will use more in the future. I first came across this approach in Kevin Flerlage’s blog post, 14 Use Cases for Transparent Shapes. It leverages a transparent shape that can be built in PowerPoint or other design tools and, unlike the other techniques, it does not rely on dashboard actions. Everything can be done in your worksheet. Because the transparent shape doesn’t have a border, or any fill, there is nothing for Tableau to highlight when it’s selected. Another way that it differs from the other approaches is that the Highlight and Filter techniques can be used almost universally (with the few exceptions we discussed earlier), but this approach has pretty limited applications when it comes to avoiding highlighting (but a wide variety of other awesome applications that you can read more about in Kevin’s post). The two use cases that we’ll focus on are text marks and buttons. Personally, I wouldn’t recommend trying this approach for any chart type where the marks aren’t uniform in size and spacing (but that doesn’t mean you can’t try it). Another downside of this approach is that it does not actually de-select a mark, it just masks it, similar to the Highlight Technique. But on the upside, it’s probably the easiest of the three methods to implement, at least for text marks.

Alright, so let’s bring that all together

Now it should be clear which technique you should use right? Of course not. It depends. So let’s look at a few specific common use cases.

Which Technique to Use When

Text Marks

As I mentioned earlier, the Highlight Technique does not work with text marks, so that leaves two options. Up until recently I would have said with 100% confidence that you should use the Filter Technique. The main difference here is that if you have more than one mark in your worksheet, the Transparent Technique will fade the other marks, and the Filter Technique will retain all formatting (so you’ll want to use other indicators, like color, to show which mark is selected). With most mark types you’re trying to avoid that fading, but it actually works pretty well with text marks. What we really want to do here is get rid of that blue box, which both options will do. Here are the two methods, the Transparent Technique as is, and the Filter Technique using color to indicate the selected mark

Transparent Technique

Filter Technique

Both look great, but I would give the edge to the Transparent Technique because of how easy it is to set up. But I encourage you to try both methods and see which one you prefer

Buttons

There aren’t really any limitations on this use case. All three methods will work, but what works best might depend on how your buttons are built and how they will be used. If you built your button in Tableau using the Circle or Square mark type, or if you built them in another tool and brought them in as custom shapes, I wouldn’t recommend using the Transparent Technique. For this technique to work you would have to create two worksheets, one with buttons and one with transparent shapes, and then layer the sheets so that the transparent shapes in your top sheet are aligned perfectly with the buttons on your bottom sheet. Not terribly difficult, but the other methods are easier in this case. The only time I would recommend using the Transparent Technique for buttons over the other techniques, is if your buttons are not actually in Tableau at all, and are instead, part of a background image. You could design and incorporate your buttons directly into your background with other design tools, and then use this technique to make them act and appear as buttons in Tableau.

The next consideration would be whether or not a button might be clicked multiple times in a row, like it would with a scroll button. In that case I would definitely go with the Filter Technique as that’s the only one of these techniques that actually de-selects the mark. As I mentioned earlier, without that de-selection, users will have to click the button three times to run the action twice in a row. If multiple clicks aren’t a concern, you can also use the Highlight Technique, but my vote goes to the Filter Technique. Overall it makes for the best user experience and can make running your dashboard actions smooth and app-like. It’s worth the little bit of extra effort to set it up.

Any Other Chart Type

If you’re running an action from any other type of chart, like a bar chart or a scatterplot, I would not recommend using the Transparent Technique. You may be able to get it to work (on a scatterplot at least), but similar to the Buttons use case, there is an easier way. I will always default to the Filter Technique, because I think it provides the best user experience. Again, it may take longer to set up, but in my experience, it’s usually worth it. However, there are times when that is not an option. As I mentioned before, with that technique, you lose the ability to ‘Clear the Selection’. That means no set controls, no filter actions, and extra work for parameter actions. If I’m running a parameter action, I will usually still put in the effort to use the Filter Technique (and use a calculated field in the action to clear the selection). If I’m running a filter action, or using set controls, I will use the Highlight Technique. So my vote here again goes to the Filter Technique (when possible).

Turn Off All Highlighting

Sometimes, I have a dashboard with no interactivity, but I still don’t want users to click on something and trigger that highlighting. If you aren’t using your tooltips you could just put a floating blank over your whole dashboard and call it a day. But in most cases I still want the users to see the tooltips. In that case, the Highlight Trick is fantastic. It’s quick to set up and it can be applied to every chart in your dashboard with a single action (except for text marks). You could set up the Filter Technique on each of your worksheets individually, or build duplicates of each and try to layer them with the Transparent Technique, but in this case, the Highlight Technique definitely gets the vote.

The Verdict

So to summarize; for text marks, like BANs, I recommend the Transparent Technique, for any other types of charts that are running a parameter action, I recommend the Filter Technique, and for charts running filter actions or using set controls, or for mass highlight removal, I recommend the Highlight Technique.

Perfect, now what exactly are these techniques?

The Three Techniques

Here’s a quick walk-through of each of the three techniques. One thing to keep in mind is that we are only going to cover how to remove the highlighting. In a lot of cases, once you remove the highlighting, it’s still important to indicate which mark is selected. This is especially true if you are using actions to filter your dashboard. I review a few ways to do this in a post I wrote a while back on the Highlight Technique, but this post is already long enough so I’m not going to repeat them here.

The Highlight Technique

  • Create a calculated field. This field can be called whatever you want and can contain any non-aggregate value. The key is that this field will be the same on every single row in your data (and every mark in your worksheet). I typically name my field ‘HL’ and use a blank value (in calc body, just enter ”). For this example, I’m going to use ‘I<3Tableau’
  • Drag your ‘HL’ field to Detail on the marks card on ALL worksheets where you want to disable highlighting
  • Go back to your dashboard and add a Highlight Action by clicking on ‘Dashboard’ in the upper section above the toolbar, and then selecting ‘Actions’. Then click ‘Add Action’ and select ‘Highlight’
  • Update your Highlight Action. In this example I want to turn off the highlighting on my bar chart and my scatterplot, so I’ve added the ‘HL’ field to detail on both of those worksheets
    • Give your action a descriptive name so it’s easy to find and edit later on
    • Under Source Sheets, select ALL of the worksheets where you want to disable highlighting
    • Under Target Sheets, also select ALL of the worksheets where you want to disable highlighting
    • Run the Action on Select
    • Under Target Highlighting choose ‘Selected Fields’
    • Choose the ‘HL’ field from the list of fields
  • Your updated action should look like this

Once that’s done, click ‘OK’ and test your action. Usually if the action is not working as expected it’s because the ‘HL’ field is not on detail, or because it’s a mark type that’s not supported by this technique (Text marks).

The Filter Technique

  • Create a calculated field called 0 and enter the number 0 in the body of the calculation
  • Repeat the step above but use 1 for the calculation name and the value in the body of the calculation
  • Right click on both new fields, 0 and 1, and change them to a dimension
  • Drag both new fields to Detail on the marks card on the worksheet where you want to disable highlighting (this technique can only support one sheet at a time)
  • Go back to your dashboard and add a Filter Action by clicking on ‘Dashboard’ in the upper section above the toolbar, and then selecting ‘Actions’. Then click ‘Add Action’ and select ‘Filter’
  • Update your Filter Action. In this example I want to turn off the highlighting on the BANs, so I’ve added the 0 and 1 fields to detail on that worksheet.
    • Give your action a descriptive name so it’s easy to find and edit later on
    • Under Source Sheets, keep the active dashboard selected in the drop-down and then select the worksheet where you want to disable highlighting from the options below (the list of sheets in your dashboard)
    • Under Target Sheets, select your worksheet from the drop-down. Make sure that you select it from the drop-down and not from the options below the drop-down
    • Run the Action on Select
    • Set the ‘Clearing the selection will’ option to ‘Show all values’
    • Under Filter, choose ‘Selected Fields’
    • In the table below, on the left side in the Source Field column, click ‘Click to add’ and choose 0 from the list of fields
    • On the right side, in the Target Field column, choose 1 from the list of fields
  • Your updated action should look like this

Once all of the options are updated, click OK and test your action. If your action is not working as expected, the most common issues are;

  • Under Target Sheets, you may have the dashboard selected in the drop-down and your worksheet selected in the options below that. You can fix this by selecting your worksheet in the drop-down instead of the dashboard
  • 0 and 1 were added to detail as measures with aggregation. If this is the cause, you’ll see a warning at the bottom of the screenshot above that says ‘Missing fields from…’. The 0 and 1 need to be converted to dimensions, or added to detail without aggregation.

The Transparent Technique

So there are actually two different techniques here, one for text marks, and one for buttons. I am going to cover the technique for text marks because I think it’s the best technique for this mark type. But definitely check out Kevin’s post here to learn more about the technique with buttons and 12 other awesome uses cases for transparent shapes.

The first step is to create your transparent shape. I usually do this in PowerPoint but you can use any design program.

  • In PowerPoint, click on ‘Insert’ in the upper toolbar
  • Click on ‘Shapes’ and select a circle
  • Click anywhere on your slide to insert the circle
  • In the ‘Shape Format’ tab, click on ‘Shape Fill’ and select ‘No Fill’
  • In the ‘Shape Format’ tab, click on ‘Shape Outline’ and select ‘No Outline’
  • Right click on your transparent shape and select ‘Save Picture As’
  • Save the transparent shape to a sub-folder in the Shapes folder in your Tableau Repository
    • The path for this is usually C:\Users\Username\Documents\My Tableau Repository\Shapes unless you changed the location
    • Within the Shapes folder you can create sub-folders. I have a folder called ‘Transparent’ with just my transparent shape, so it’s easy to find

Once your transparent shape is created and saved you can apply it to your Text marks.

  • Go to the worksheet with the text marks
  • On the marks card, change the mark type to ‘Shape’
  • Click on ‘Shape’ then ‘More Shapes…’
  • At the bottom right of the window, click ‘Reload Shapes’
  • In the ‘Select Shape Palette’ drop-down, select the sub-folder where you saved your transparent shape
  • Click ‘Assign Palette’

When you do this, Tableau automatically moves all of your text to the Labels for the transparent shape, but if you have a field on color, you may notice that the color isn’t applied to those labels. To fix this, there’s one additional step.

  • Click on ‘Label’ on the marks card
  • Click on ‘Font’
  • Click the ‘Match Mark Color’ box

Now the color should be applied to each mark and if you click on any of the text marks, the blue box should be gone. You should see the selected mark retain it’s formatting, and the non-selected marks fade slightly.

The End

So that’s it for this installment of ‘It Depends’. Please keep in mind that these recommendations are all personal opinions based on my experiences. I encourage you to learn and try all of these different techniques on your own and figure out what works best for you.

And I hope you’ll join us for the next installment of ‘It Depends’ where we’ll discuss the different methods for filtering a dashboard with an action that supports multiple selections (Filter Action vs Set Action vs Set Control vs Parameter Action). Thanks everybody!