# The Tableau Order of Operations

In my
opinion, Tableau’s Order of Operations is a topic that every single Tableau user needs to be
intimately familiar with. If you do not understand the Order of Operations,
then you’ll find yourself constantly frustrated as you try to troubleshoot
something that just isn’t quite working as you expect it to. So, in this blog,
I’m going to briefly introduce the Order of Operations and each of its
components. Then I’m going to share five common Order of Operations problems
that I regularly encounter, along with how to address them.

**What is the Order of Operations?**

The Tableau Order of Operation is similar to the Order of
Operations in mathematics, which we all learned early in our school careers.
You remember it—first comes items within parentheses, then powers, then multiplication
and division, followed by addition and subtraction. Tableau’s Order of
Operations is quite similar, indicating the order in which different Tableau
operations—filters, calculated fields, and other Tableau features—execute and/or
compute. Here’s Tableau’s Order of Operations.

On the left, we can see the major types of filters and, on
the right, we can see other types of Tableau features such as LOD calculations,
table calculations, totals, and reference lines. The order flows from the top
to the bottom. It is important to note that the Order of Operations does not
include every Tableau feature. For example, the Pages shelf is not included (if
you’d like to see Tableau create a more detailed Order of Operations, please
upvote the following: Improved documentation of Order of Operations).
However, even without every component, the graphic is quite a useful tool for
understanding how Tableau works and is particularly helpful when you are
troubleshooting a problem.

I’m not going to explain each of these features in detail,
but let’s quickly step through them in order. As we can see, the first feature
to be computed are

**Extract Filters**, followed by**Data Source Filters**(for a great discussion of the difference between these two filters, see Difference Between Extract Filters and Data Source Filters).
Next in line are

**Context Filters**, followed by**Sets, Conditional Filters, Top N Filters, and Fixed LODs**, which are then followed by**Dimension Filters**. As we’ll see in the**Common Problems**section coming up, this is where we often start to see a variety of issues with the Order of Operations. This is because we need to think carefully about whether we want our filter to compute before or after things like Top N filters and Fixed LODs. Those filters that need to compute*before*will have to be converted to context filters. Those which need to compute*after*can remain dimension filters (the default).
Next, we have

**Include/Exclude LODs, and Data Blending**. While it might seem that we’d have similar issues with Include/Exclude LODs as we do with Fixed LODs, those problems are much less pronounced since these compute after both context filters and dimension filters. Thus, whether you use a context or dimension filter has little effect on how Include/Exclude LODs compute. That said, as we’ll see below, we can convert our Fixed LODs to Include/Exclude LODs in order to trick the Order of Operations.
Next we have

**Measure Filters**, which in my experience, do not typically cause many Order of Operations problems.
Then we have

**Forecasts, Table Calcs, Clusters, and Totals**, followed by**Table Calc Filters**. This is another area where we start to run into some Order of Operations problems. As we’ll see below, this is largely due to the nature of table calculations and how they compute.
And the final items on the Order of Operations are

**Trend and Reference Lines**.**Common Order of Operations Problems**

Now that we’ve introduced the Order of Operations, let’s
talk about a few of the most common Order of Operations problems. To be clear,
these are not the

*only*problems you’ll ever encounter, but in my experience, they tend to be things that we have to deal with fairly regularly.**1) Dimension and Context Filters with Fixed LODs**

Let’s
start with an example. We want to know the date of each customer’s first order.
So, we first create a Fixed LOD to get the first order date.

__First Order Date__
// Get
the first order for the customer.

{FIXED [Customer Name]: MIN([Order Date])}

Then we
build a view showing each customer and their first order dates.

This works
great, but what if you want to apply some filters? For instance, let’s filter on

**Category**and select only**Furniture**and**Technology**.
You
might expect that Tableau will show us the first order dates for just those two
categories, but as you can see above, the dates remain unchanged. So, what’s
going on here? Let’s break it down in terms of the Order of Operations. We have
a Dimension Filter on

**Category**and we have a Fixed LOD.
As we
can see, the Fixed LOD comes before the dimension filter. Thus, the LOD
computes first, finding the overall first order date for each customer. Only
after Tableau has computed the first order date, does it filter the view. This
is why the dates aren’t changing. To be clear, the dimension filter is not
pointless in this case. It will remove any customers who did not purchase any
Furniture or Technology—it just won’t have any impact on the dates.

If we
want to force the filter to compute

*before*the LOD, we need to change it to a context filter. We can do this by right-clicking the filter and choosing “Add to Context”. You’ll notice the difference visually as context filters show as grey pills.
Since
this is now a context filter, it will compute before the LOD and the First
Order Date values will change.

This,
in my opinion, is probably the most common Order of Operations problem I encounter,
so it’s really good to understand how dimension and context filters work with Fixed
LODs.

**2) Fixed, Exclude, and Include LODs…Oh My!!**

Sometimes
you may find yourself in a situation where you need a single filter to apply to
one LOD but not to another. Unfortunately, a filter can only be one or the other
so we will find ourselves in an Order of Operations bind. For example, let’s
take our view from above and add one more requirement. We wish to compare each
customer’s max sales for the selected categories (based on the filter) to the
max sales for

*all*categories. Both calculated fields will use the same LOD

__Max Sales Filtered__
// Max sales for the customer.

{FIXED [Customer Name]: MAX([Sales])}

__Max Sales Overall__
// Max sales for the customer.

{FIXED [Customer Name]: MAX([Sales])}

However,
we want the

**Category**filter to compute*before***Max Sales Filtered**, but*after***Max Sales Overall**. This simply is not possible because, as we noted above, a filter cannot be both a dimension filter and a context filter.
So how
do we address this need? Well, first of all, we’ll need to change the context
filter to a dimension filter because there are no LOD types that can compute
before context filters. Now that our filter is a dimension filter, both of our LODs
will compute before the filter, giving us the same values.

But we need
to find a way to move

**Max Sales Filtered**down in the Order of Operations so it computes*after*the filter. We can do this in one of two ways. We can either change the calculation to use an Include or Exclude LOD or we can change it to use a Table Calculation since all of these compute after dimension filters.
Here’s
an Exclude LOD which should do the trick:

__Max Sales Filtered__
// Max sales for the customer (Exclude LOD).

{EXCLUDE [First Order Date]: MAX([Sales])}

And the
table calculation would look like this:

__Max Sales Filtered__
// Max sales for the customer (table calculation)

WINDOW_MAX(MAX([Sales]))

While
some of the filtered max sales will match the overall max sales (because the
max sales for those customers were for either Furniture or Technology), we can
see some instances where they differ, indicating that our calculations worked.

**3) Dimension and Context Filters with Top N Filters**

In # 1,
we address problems with dimension filters, context filters, and Fixed LODs.
The third problem we’ll address is similar but addresses Top N filters instead
of Fixed LODs.

For
this example, we’ll build a view showing the top 15 customers by sales. We’ve
done this by creating a Top N filter on

**Customer Name**. We also have a dimension filter on the**Year**of the**Order Date**.
In this
case, our

**Sales**value is just a normal aggregation—we’re not using any LODs so they are out of the picture.
This
works great—we can see our top 15 customers nicely. But, if we filter down to
just 2017, our Top N filter seems to break as we now only get the top 12.

So,
what’s happening here? Once again, let’s look at the Order of Operations and
break it down. We have one Top N filter and one Dimension Filter as shown
below.

But, as
we can see, the Top N filter computes

*before*the dimension filter. So, Tableau is first getting the overall top 15 from the entire data set. Once it’s computed the top 15, it then applies the Year filter. In this case, 3 of our top 15 had no sales in 2017, so they are removed from the view, leaving us with just 12.
If we
want to make sure that our Top N filter computes

*after*our Year filter, then the solution is the same as in # 1. We simply add the Year filter to context.
Note: It
is possible to add a Top N filter to context, making it both a context filter
and a Top N filter. In this case, it will compute as a context filter—before Fixed
LODs, other Top N filters, etc.

**4) Index vs Top N**

This one
is not so much of a problem as something I’d just like to point out. I often
see people using INDEX to show the Top N. To use the example from # 3, we could
create an INDEX() calculated field then use it as a filter, keeping values
1-15.

In this
case, it would not matter if the Year filter were a context filter or a dimension
filter (Note: It might matter from a performance standpoint, but not functionally).
This is because the filter on INDEX is a table calculation filter, which computes
near the end of the Order of Operations.

So when
should you use a Top N filter vs INDEX? As is almost always the case, it
depends—on what other types of filters and features you are using on your view,
when you wish for the filter to compute, etc. My general recommendation is to use
Top N filters as your default as these tend to be more straightforward and do
not come with the inherent complexities of table calculations. But, when
needed, INDEX can be a great option for performing a top N filter.

**5) Table Calcs and Table Calc Filters**

Since
we’re on the topic of table calculations, my final common Order of Operations
problem will deal with these beasts. For this example, I’ve created a worksheet
that ranks each city/state by sales using a simple RANK calculation.

What we
want is to be able to filter this by state, but still see the national ranks. If
we simply filter on State, then the Rank is recalculated as shown below.

This,
of course, is because of the Order of Operations.

Table
calculations compute

*after*dimension filters, so the view is first being filtered, then the rank is being computed. So, how do we ensure that we retain the original overall Rank, while filtering the view? To do this, we need the State filter to compute*after*the table calculation. And the only filter that computes after table calculations are Table Calc Filters. So, if we can somehow force our State filter to be a table calc filter, this should work. To do this, we can use a trick I learned from the inimitable Pooja Gandhi. We can use LOOKUP with an offset of 0 as shown below.

__State TC__
// Force
state to be a table calc...

// ...so
it will compute after the Rank.

LOOKUP(MAX([State]), 0)

Then we
can use this as our filter.

Because
Table Calc Filters compute

*after*table calculations, the Rank is first computed, then the view is filtered, allowing us to maintain the overall rank of each city.
This is
one of my all-time favorite tricks—you might even see it in an upcoming tips
blog. It may seem like something that would only be used in rare situations,
but I actually find myself using this all the time to trick the Order of
Operations and bend Tableau to my will.

And
this LOOKUP trick is just one example where we can use Table Calc Filters in
this way. I won’t show this in detail, but one common scenario is where you
might be using a table calculation to show change year over year, but only wish
to display the latest year. Table calculations, in order to compute properly,
will require you to have the previous year on the view. But we can use Table
Calc Filters, using functions such as LAST to filter out unnecessary years

*after*the table calc has been computed.**That’s a Wrap**

Understanding
Tableau’s Order of Operations is absolutely critical to gaining a deep understanding
of and mastery over Tableau. Without this knowledge, you’ll find yourself
constantly confused about why the software is doing what it’s doing. But, once
you understand the Order of Operations, you’ll be able to make Tableau do just
about anything you can imagine.

While I’ve
only shown you a couple of specific examples in this blog, I hope that you can envision
how you might apply these basic techniques to your own work, even if your scenarios
are not exactly the same as the ones I’ve shown. Additionally, it’s important
to note that my examples have only scratched the surface. My goal was to show
you some of the most common Order of Operations problems I’ve personally
encountered. But there are many other scenarios that you may run into your
work. That said, I assure you that, if you learn the Order of Operations and
when to apply it, there is almost nothing you won’t be able to overcome.

As always,
thanks for reading and feel free to share your thoughts in the comments section
below.

Ken Flerlage, September 28, 2020

Hi,

ReplyDeleteI have a doubt about your calculations Using Exclude & Table Calculation of Window Max.

How this two functions will help to compute Customer Sales for any 1 or 2 selected Categories from Category Filter? e.g. Furniture & Technology

I don't quite understand what you're asking. Perhaps we could address this question offline? Please feel free to email me. flerlagekr@gmail.com

DeleteCan you share Sample Dashboard to explain below Text from your Post?

ReplyDelete"one common scenario is where you might be using a table calculation to show change year over year, but only wish to display the latest year. Table calculations, in order to compute properly, will require you to have the previous year on the view. But we can use Table Calc Filters, using functions such as LAST to filter out unnecessary years after the table calc has been computed."

I'm not entirely sure what you're asking. Could you email me at flerlagekr@gmail.com?

DeleteGreat and generous sharing. Thanks Kevin and Ken!

ReplyDelete