Working with Table Calculations in Tableau

Today, we will look at basic Table Calculations in Tableau.  Table calculations are the computational workhorse of Tableau.  They allow you to calculate values outside of the traditional realm of "Slice by X Dimension."  Tableau has a few built-in table calculations, we will use the "Running Sum."  We will also look at the Index() function and how to properly use the "Compute Using" property.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a table with a dimension and a measure

Sales by Container

Step 2:

• Right-Click [Sales] in the "Text" Shelf -> "Quick Table Calculation" -> "Running Sum"
• Add [Sales] to the graph again to be sure the Running Sum is working as it should.

Running Sum of Sales by Container

Now, you can see that the Running Sum is the sum of all Sales in preceding categories.  This is extremely handy in quite a few places.  Now, let's look into the Index() function and the Compute Using property.

Step 1: 

• Create an empty table with two dimensions

Blank by Order Priority and Category

Step 2:

• Create the following calculated field:

Index

By default, Table Calculations typically use the Compute Using setting of "Table (Across)", which means it runs across the graph.  See the following pictures:

Blank by Order Priority and Category (Column Index)

Blank by Order Priority and Category (Row Index)

Just like the name implies, it runs across the table.  However, we can force it to run other directions or even across a specific variable.

Step 3:

• Add [Sub-Category] to the Rows Shelf
• Right-Click [Index] from the Measures Shelf -> "Edit"
• Click the blue text "Default Table Calculation"
• Choose "Compute Using" -> Sub-Category
• Add [Index] to the Rows Shelf

Blank by Category, Sub-Category, and Order Priority (Row Index on Sub-Category)

Now, you can see that the Index runs along Sub-Category with no regard to the fact that Order Priority is also there.  Mastering Compute Using is essential to mastering Tableau.  Try using more complicated indexes, or maybe some more of the default table calcuations.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Creating a Dashboard with Variable Container Heights in Tableau

Today, we will create a dashboard with containers that change size based on what they are displaying.  This is especially useful when you want to hide/show certain details.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create the following dashboards:
• Profit by Category
• All Additive Measure by Customer (Sorted by Highest Profit)

Profit by Category

Customer Information

Now, we want to put these in a dashboard that shows only the Profit by Category.  Then, when you select a category, the bar graph shrinks to make room for the Customer Information.

Step 2:

• Create a new dashboard
• Drag a "Vertical Layout Container" onto the dashboard
• Drag "Profit by Customer" onto the Layout Container
• Set the Fit to "Entire View"
• Drag "Customer Information" below "Profit by Customer"
• Set the Fit to "Fit Width"
• Set the Dashboard Size to "Exactly -> Laptop" (You can choose any size you want)

Dashboard with no Actions

Now, it's starting to look more like what we want.  However, we need to create an action to make "Customer Information" filter on Category and hide when there is no selection.

Step 3:

• Create the following action

Category Filter

Now, when you click on a Category, "Customer Information" is filtered.  

Filtered Dashboard

Then, when you deselect the Category, the bar graph expands to hide the empty "Customer Information" chart.  This is because we put the bar graph in a "Vertical Layout Container" with fit "Entire View."

Unfiltered Dashboard

While this is a simple example, there is so much more to do.  Try using graphs oriented side-by-side, or try using more complex actions.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Creating a Top N filter with an "Other" Category in Tableau

Today, we will create a Top N Filter that also displays an "Other" category.  This can be extremely useful when you want to see how the Top N compare to the rest of the population.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create the following calculated fields:

Determines which values are in the Top 20

Determines which value will start the "Other" category

Returns true for the Top 20 and the first value in the "Other" Category

Returns SUM( Sales ) for the Top 20 and returns the remaining sum for Other

Return Customer for the Top 20 and Other for the 21st

Step 2:

• Rows Shelf: "Customer", "Top 20 Customer Label"
• Remove Header for "Customer"
• Text Shelf: "Top 20 Sales"
• Filters Shelf: "Top 20 + Other" = TRUE
• Sort "Customer" Descending by Sum of "Sales"
• Edit Table Calculation "Top 20 Sales" using "Table (Down)"

Top 20 Customers by Sales with Other

Now, you have created a top 20 filter with an "Other" category.  As you may have noticed, this is free of any dimension constraints (other than the label).  So, you can drag any other dimensions you want onto this graph as long as you create a new label for it.  Also, you can change your measure by altering "Top 20 Sales" and changing the measure that you sort by.  Try using this with more complicated metrics, or adding a parameter.  There's so much more to do here, have some fun.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Creating Custom Color Palettes in Tableau

Today, we will look at creating custom color palettes in Tableau.  Tableau has some great options for coloring your charts.  However, you may find yourself in a situation where you don't feel that Tableau's options are sufficient.  So, you can create your own custom color scheme.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Decide whether your color palette should be "Regular", "Sequential", or "Diverging"

Here are some pictures of each of these types of palettes.

Regular

Sequential

Diverging

Step 2:

• Determine what colors will be in the palette.

For a regular palette, you must have a "discrete" field, such as "Product Category."  Then, you can choose any number of colors to be in your palette.  For a sequential palette, you must have a "continuous" field, such as "Sales."  Then, you can choose any number of colors to make up your sequence.  For a diverging palette, you must have a continuous value as well.  However, for a diverging palette, you can only choose your start and end colors.

Step 3:

• Open "My Documents\My Tableau Repository\Preferences.tps"
• Insert your Color Palette using the format below.

Preferences.tps

Here, I created a palette named "Orange-Green 1:1" of the type "Ordered-Sequential."  Then, I listed the colors in the sequence using "RRGGBB" format.  Using this method you can create all manner palettes.  Here are some of the palettes I've created for various projects.

Diverging with Black in the Middle

Diverging with Black at the End

A keen observer will notice that these "Diverging" palettes are actually "Sequential" palettes with a specific sequence.  This is because Tableau only allows you to define the start and end colors for a "Diverging" palette.  Also, you should not focus too heavily on what the palette looks like in this window.  It's wise to use the advanced options to control where each color begins and ends.  Have fun when you create your colors.  You never know what kind of illustrations you can create for the user with a little cleverness.  For another route, you can bin your "continuous" variable and then assign a "Regular" palette to the bins.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Creating Management Dashboards in Tableau

Today, we will talk about "Management Dashboards" in Tableau.  In a previous post, I said that a dashboard should fully address one scenario; however, that is only one type of dashboard.  A "Management Dashboard" is a single glance overview of a set of scenarios, with no actionable information attached.  It is usually set up in a 2x2 or 3x3 grid where each graph shows a different metric.  In a perfect world, any deviation from the norm would be immediately visible to the user.  As usual, we will use the Superstore Sales sample data set in Tableau.

Example Metric: 

• Determine whether a combination of Segment and Sub-Category was profitable in the last month.

Heat Map of Profit by Customer Segment and Sub-Category

Here, we can easily see which Segments and Sub-Categories are profitable.  If we were to combine this chart with a few other similar charts on a dashboard.  We would have a simple "Management Dashboard."  There are a few things to note about creating effective "Management Dashboards."

Note 1:

• Notifications should be consistent across all graphs.  For example, Orange should always be bad, or Large Box should always be bad.

In my experience, this is the most common mistake made when creating a "Management Dashboard."  When one graph is a heat map with Orange being bad, while another graph is a bar chart where Large is bad.  To make it even worse, you could have another graph that uses the color Orange, yet it is completely unrelated to the decision to be made, e.g. Orange denotes  Southeast Region.  

Failure to adhere to this strategy causes the user to have to look individually at each graph to determine what they mean.  Even worse, they may misconstrue that the Southeast Region is performing poorly because it is Orange.

Note 2:

• Allow for drill-through.

When the user sees something alarming, they need to be able to take action on that.  If it is not immediately obvious how they should act, then allow them to click on the "Bad" object, i.e. an Orange box, and be directed to another chart which shows in-depth information about why that box is Orange.

This concept should also be consistent throughout your graphs.  It is unwise to assume that the user has as much knowledge about the data as you do, especially if you are knowledgeable enough to create new metrics about your data.

Note 3:

• Limit the scope of the dashboard to a particular audience.

If the audience for your dashboard is Regional Sales Managers, there is probably not much use in telling them how the company is doing as a whole.  I try to work with the overlying concept of "Less is More!"  Tell them exactly what they need to know and no more.

I hope you found this informative.  If you have any comments or questions, feel free to comment.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Creating a Period to Date Matrix in Tableau

Today, we will talk an idea I recently had for working with "Period to Date" values.  Tableau can easily create PTD values.  Here, I create daily, "Month to Date", and "Year to Date" values in a text table.  As usual, we will use the Superstore Sales sample data set in Tableau.

Period to Date Text Table

If I want to add more measures, I can add them to the columns.  However, the client wanted the table in the following format:

Period to Date Matrix Mock-Up

Here, the report date is always the previous day.  This means that we no longer have a time dimension in our graph.  Also, our measures are not on the same shelf as our table calculations.  How can we accomplish this?

Step 1:

• Create the following calculated field

Period to Date Calculated Field

This field applies the label "Daily" to every record on the "Report Date", "Month to Date" to every record in the Month of "Report Date" (but not to those that already have the "Daily" label), and "Year to Date" to every record in the Year of "Report Date" (but not to those that already have the "Daily" or "Month to Date" labels).  A sample of this data, if the report date was 1/1/2012 would look like

Calculated Field Mock-Up

Step 2:

• Add "Period to Date" to the "Columns" Shelf
• Filter "NA" out of "Period to Date"
• Add your measures to the "Text" Shelf

Period to Date Matrix

An astute reader might say, "Wait!!! Month to Date doesn't look at the complete month, and Year to Date doesn't look at the complete year."  You're right!  The "Month to Date" column is missing the "Daily" values and the "Year to Date" column is missing the "Daily" and "Month to Date" values.  This sounds like a running sum issue.

Step 3:

• Change each measure to a running sum across the row

Period to Date Matrix

If we look back at the first picture in this post, we can see that the "MTD Sales" value at the end of January is $340,627, which matches our "MTD Sales" value here for the month of January.  This is evidence that our logic is correct.  Now, we've created the exact chart the client wanted and they have showered us with giant piles of money (I may or may not be exaggerating).

One issue with this method is, it requires that the measures be additive, or at least made up of additive components.  Feel free to play around with this method.  You can create a parameter or calculated field that would allow you to change the "Report Date" to your liking.  I hope you found this informative.  Thanks for reading.

EDIT: I found an issue with this methodology.  If the data date is the first day of the month, first month of the year, etc., you will not have "Month to Date" or "Year to Date" in your data set, respectively.  The workaround we used was to move the Period-To-Date function down into the SQL query and union hard-coded rows onto the data set for each missing value of the Period-To-Date function.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Using Cascading Filters in Tableau

Today, we will talk about how to create "Cascading" Filters in Tableau.  "Cascading" Filters are a set of filters in which the contents of a quick filter is affected by the selection in a previous filter.  The simplest approach to this is through hierarchies; however, they are not necessary for this approach.  It is important to note that "hierarchy" refers to Tableau's concept of a hierarchy and does not require that the underlying data be implement in a hierarchy, such as in SQL Server.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a worksheet

Sales by Customer Segment

Now, let's say we wanted to filter on "Category" and "Sub-Category" with "Cascading" Filters.

Step 2:

• Add "Category" and "Sub-Category" to the "Filters" Shelf
• Choose "Show Quick Filter" for both fields
• Click the "Down Arrow" in the Upper-Right Corner of the "Sub-Category" quick filter
• Select "Only Relevant Values"

Sales by Customer Segment (with cascading filters)

Now, when you select a "Category", only relevant "Sub-Categories" will be shown. In this case, this was the default behavior.  However, it is important to note that you can change this behavior by selecting one of the other options, "All Values in Database" and "All Values in Context."  If you don't know what "Context" is, check out Types of Filters in Tableau.  Also, from a usability standpoint, subsequent filters should always be beneath their parents.  This presents a logical flow.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Dynamically Choose the Fields Displayed on a Chart in Tableau

Today, we will talk about using parameters to change the fields that are displayed on your chart.  For instance, let's say you have 5 metrics for each of 6 measures.  You don't want a chart with 30 columns; so you need to allow the user to select the measure and only see metrics associated with it.  You could filter on "Measure Names", but this will quickly become unwieldy.  As usual, we will the Superstore Sales sample data set in Tableau.

"Measure Names" Quick Filter

Step 1:

• Create a Parameter named "Measure Parameter"
• Give it the data type "String"
• Select "Allowable Values: List"
• Enter your list of measures, e.g. "Sales", "Profit", "Quantity"

Measure Parameter

Step 2:

• Create a Calculated Field named "Measure"
• Enter the CASE Statement as below

Measure

Now, you have a Calculated Field that takes a specific measure based on which value the parameter takes.

Step 3

• Use "Measure" in place of the Standard Measures
• Right-Click "Measure Parameter" -> Select "Show Parameter Control"

Chart with Dynamic Measures

Now, you can select which measure you want to display, and your chart changes in real time.  There is so much to be done here.  You can even change your titles to display dynamically as well.  Feel free to explore the multitude of possibilities.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

Formatting Individual Columns in Tableau (Dual-Axis Crosstabs)

Today, we will look at a special type of graph in Tableau, the "Dual-Axis Crosstab."  The inspiration for this method comes from Conditional Formatting, which I know was at least partly developed by Jonathan Drummey.

In it's basic form, Tableau can only format based off one value.  For example, you can have a chart that displays Sales and Profit, but you can only color based on one of them.

Profit and Sales by Region (Colored by Profit)

Now we have an individual color for each row, when we really want one for each cell.  We can work around this by dragging "Measure Values" to the color shelf.  While this gives us an individual color for each cell, it puts them all on one scale, which is foolish in this case.

Profit and Sales by Region (Colored by Measure Values)

What we really need is to be able to apply an individual formatting to each column.  This is where the "Dual-Axis Crosstab" comes in.

Step 1: 

• Create a meaningless calculated field

Calculated Field

This field will be used as the base for our columns.

Step 2: 

• Drag the Calculated Field onto the "Columns" Shelf
• Change the "Mark" Type to "Text"
• Right-Click inside the Column -> Select "Format" -> Click the Icon that looks like a Pen, -> Remove "Zero Lines"
• Right-Click on the Axis Header (which is ironically at the bottom) -> Select "Edit Axis" -> Change the Title to something reasonable
• Still in the "Edit Axis" Window, open the "Tick Marks" Tab -> Select "None" and "None"
• Drag the Desired Measure onto the "Text" Shelf
• Resize the Column

Single-Column (Dual-Axis?) Crosstab

Now, you've created a "Single-Column Dual-Axis Crosstab."  Realistically, we aren't going to use the "Dual-Axis" component in this post; however, I'm not sure what else to call it.

Step 3:

• Drag the "Field" Calculated Field to the "Columns" Shelf and place it beside the previous one
• Next to the Word "Marks" above the "Marks" Shelf, Select the "Down Arrow" -> Select "Multiple Mark Types"
• Now, you can cycle between your different fields
• Drag another measure to the "Text" Shelf for the new field
• Repeat "Step 2" to clean this column up as well

Dual-Axis Crosstab

Now, you can apply individual formatting to each column by cycling through them on the "Marks" Card.  For example, you can color the "Profit" Column by "Profit" and color "Sales" by "Sales."

Step 4:

• Repeat the above process for adding any more measures you want
• Add any dimensions you would like to the "Rows" Shelf

Completed Dual-Axis Crosstab

Now, you can see that all "Orange" values should be looked into.  While this is a very simple representation, there is so much more that can be done here.  Feel free to experiment.  There's much more information to be found in the link at the beginning of the post.  Thanks for reading.

EDIT:  Some readers have been asking for a workbook illustrating this concept.  Zen Master Jonathan Drummey has a very good workbook illustrating this technique posted here.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn