How to Uncover Tableau’s “Silver Bullets”

Did you know there’s some functionality within Tableau that you may not be privy to?

Surprise! We call these “Silver Bullets” and this post will focus on three of them. I will be examining:

• How the power of the Index feature helps with customizable sorts
• Forklifting to isolate columns on crosstabs and create donut charts
• Several development skills

The Power of Index

Index is a table calculation that returns the position of the partition (cell) in a table. If you were to use Index and compute Table Across, you would see the following:

Notice as your move across the table, the numbers increase by 1. You can use this same functionality for Table Down, Pane Down, Pane Across, just like you would with any Table Calculation. We can leverage this Index function to sort a crosstab in more dynamic ways than usual. Let’s say we have the following crosstab:

We would like to sort the following crosstab by a measure and show only the top 10 for that measure. Typically, we can only show the top 10 or N by just one measure. The user would have to keep changing the filter in order to dynamically see a different measure sorted by the top 10. However, using Parameters and the Index function, we can sort the top N by any measure and the table will dynamically change.

First, we need to create a Parameter, which we’ll call “Records Per Page.” This name is being used because this will eventually lead us into our next skill within this Silver Bullet. The parameter will need to be a float and since we want the top 10, we can set the current value at 10. In order to get this Parameter to engage, we will create a calculation that will return the Index if the Parameter is greater than or equal to the Index. The calculation will read:

INDEX<=[Records Per Page]

This calculation will need to be brought into the filters shelf and filtered to “True.” You can now sort by different measures and change how many records you are sorting by. In the example above, if we sorted on Sales and showed only the top 10, we would have the following shown:

Now that we have a dynamic sort, we may want to look at the next 10, such as 11-20 or 21-30. We can do this by setting up a “Page” Parameter. The “Page” Parameter should be set to Integer and the range of values is up to the developer in terms of how they want the user to interact with this page feature. Once the Parameter is created, then the following calculation needs to be created:

INDEX()>=[Records per Page]*([Page]-1) AND INDEX()<=[Records per Page]*[Page]

This calculation will only return rows of data in between the highest and lowest records based on what page you select. If you want to see the top 10 and select page 2, you will only see records 11-20.

Forklifting

Forklifting is method where a user creates a “dummy” measure so that you can set up an individual Marks Card for each Measure or Dimension you decide to use.

In the example above, we created a dummy measure of 1 and set it to Minimum. We placed each of these measures onto Columns, which allows the user to edit the single Measure or Dimension in a crosstab as opposed to having to apply a Marks setting for all of the Measures or Dimensions. In this example, notice how the colors for each column are different and actually reflect the colors for the individual measures within the columns. HDR stands for “Header” and this is simply a calculation with each of the header names with appropriate spacing in order to appear as actual headers (since headers are not available for each of these Measures when forklifting).

The forklifting technique allows us to also create Donut Charts. These are Pie Charts with a hole in the center, as shown below:

We simply create a dual axis chart with the “1” measure and bring those to rows, and on the Marks Card, make these a pie chart. One of the pie charts can be labeled and created as we would a normal pie chart. The other pie chart (the donut hole) will need to be sized smaller and match the background of your worksheet.

Development Skills

This final section will discuss a few developmental skills you should consider when building out worksheets and dashboards. The first developmental skill will focus on coloring sheets.

Oftentimes, people color their sheet tabs to match up with the dashboards they appear on (e.g. all green sheets are on the green dashboard, all red sheets are on the red dashboard, etc.) You can also color your sheet tabs to differentiate the stages of development a sheet may be in. For example, you may color your sheets the following way:

• Red = Open Questions
• Blue = Completed
• Yellow = In Progress
• Purple = Alternative

This allows for other people editing and analyzing your dashboard to track progress, and helps you prioritize what you may need to work on. You can also use captions to be more specific with information and questions.

The other developmental skill focuses on using parameters with dates. Oftentimes, users use TODAY to check most recent data. By using a parameter in place of TODAY, users can back check data instead of relying on data up until today. The parameter may be created like this:

Using this type of parameter is great practice for post-development to check that your data is correct.

The final developmental skill involves using thick borders when utilizing containers in dashboards. When building dashboards, users sometimes utilize multiple containers. However, these containers can get lost in the shuffle when we are developing our dashboards. By using thick borders on these containers, it makes the process more efficient when trying to figure out what containers are being used. Notice the example below:

There are a few containers being used in the above example. If we were to make edits to these containers, we may end up confusing how many containers are being used. By using thick borders during the creative process, we are able to identify where our containers are and can make adjustments appropriately. Obviously, once we have our dashboard laid out the way we intended it to be, then we would remove those borders.

Now you’re armed with the knowledge of Tableau’s Silver Bullets and ready to take advantage of these lesser known features to create the most effective dashboards.

Want to see how you can take your Tableau projects to the next level? Learn more about how our experts can help grow your skills today.