Creating Table Calculations on Values outside of the Filter Range in Tableau

Today, we will talk about creating table calculations for values that are being filtered out of your view in Tableau.  A common scenario for this is as follows: "I want to see this month's sales as well as a 12-month rolling average for last year."  Let's naively put this is a chart.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a 12-Month Rolling Average
• Create the following calculated field

12-Month Rolling Average

We will use this calculation throughout this post, so don't go deleting it or creating a new workbook.

Step 2:

• Drag Month to Columns Shelf
• Drag Sales and 12-Month Rolling Average to the Rows Shelf
• Turn on Dual Axis and Synchronize the Axes
• Turn Sales into bars and 12-Month Rolling Average into a line
• Filter the year to be 2012

Naive Rolling Average

There are two very important things to notice about this "Rolling Average."  To start, the first value in the line is exactly the first month's sales.  Also, the line gets more and more stable as we progress in time.  This is because our rolling average doesn't consider any values outside of our filter.  The only value that is a true 12-month rolling average is the value for December.

We can fix this by exploiting the order in which operations are performed in Tableau.  We want our table calculations to be evaluated before the data is filtered on year.  We know that Tableau executes all table calculations simultaneously.  Therefore, if we can use a table calculation to filter on year, then we will force Tableau to evaluate our table calculations before it filters on year.  Let's do this.

Step 1:

• Examine the structure of the graph without a date filter

Unfiltered graph

Using this graph, we want to hide the first 36 bars (months) and show the last 12 bars (months).  There are many ways to do this; we will use the INDEX() function because it seems to be the most intuitive in this situation.

Step 2:

• Create the following calculated field

2012 Filter

Step 3:

• Make "2012 Filter" Discrete
• Filter "2012 Filter" to be 1

True Rolling Average

Notice that the line is much smoother the entire way across the graph, indicating that it is evaluated properly.  There is no simple answer to this problem when you encounter it in your work.  You must be able to create your own clever table calculation depending on how your data looks.  Remember, there are plenty of table calculations to help with this, such as FIRST(), LAST(), or even WINDOW_SUM().  Depending on your situation, you may even be able to use the Pages Shelf.  I hope you found this informative.  Thanks for reading.

EDIT:

Joe Mako commented with the more elegant method of using LOOKUP( MIN( YEAR( [Order Date] ) ), 0 ) instead of the index on the months.  This just goes to show that there are many different ways to handle this situation.  Many thanks to him and all of the readers.

Brad Llewellyn

Associate Consultant

Mariner, LLC

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