The Order of Operation is the single most important thing you need to understand as you progress in your journey. It is the root cause of most mistakes I see in questions posted on the Forum – So what is it –
It is the sequence that Tableau goes through each time you create a viz. There are several models – the one I use consistently is 10 steps in 3 groups:
Each worksheet in Tableau has its own data table – a subset of the total data set that was uploaded. That subset is created first by filtering out unneeded data at the workbook level (Steps 1-2) then creating and filtering a table structure – think like a spreadsheet even though it is a tall narrow structure internal to Tableau) In Steps 3-5 and the remaining steps load values into the table, do some calculations, and create the viz.
The first 2 steps operate at the “Workbook Level” and filter out data as files are uploaded into Tableau reducing the data volume and improving performance. They are very easy to use – on the data source tab – Add a filter – select a dimension or measure and then the values you want to filter out:
It’s no more difficult than that!
The use of the Context filter in Step 3 and the effect on Fixed LODs, Top N, and Set formation causes much confusion for new and more experienced users.
It is not difficult – just follow a simple rule –
If you want the filter to affect the results of the Fixed LOD, the Top N or the creation of a set then place the filter in Context – If not then use Dimension filters that are applied in Step 5.
In this example, I’ve written a LOD to total the Sales at the Category level:
{ FIXED [Category]: sum([Sales])}
And used it to calculate the percent of Sales by category
sum([Sales])/sum([Fixed LOD Category Sales])
Category and Sub-categories are on rows and the Fixed Category, Sales, and % to Category measures make up the chart – A filter will be applied to Sub-Category
First Sub-Category filter is applied in Context before the LOD is calculated so the Fixed Category totals are affected and the result changes with the filter –
Now the same filter is applied as a Dimension filter After the Fixed Category LOD is calculated so the totals are not affected by the filter – (Note the filter is NOT ignored – you as the developer used a dimension filter (Step 5) not a context filter Step 3 and filtering is done after the LOD is calculated Step 4))
Data from Blended data sets is loaded in Step 6 of the Order of Operations – After all, filtering has been applied and after Fixed LODs, Top N and Set are created – Remember when we talked about connecting data tables and specifically about some of the limitations of Blended? The Order of Operation is immutable which is why you can’t use data from Blended data sets in Fixed LODs, Top N, or Set, and why filtering is limited to the level of the link between the data sets (More on that in the next Session)
Include and Exclude were 2 of the most difficult functions for me to understand when I first started using Tableau. It took some work but I think this explains the concept –
Think of Include as setting a limit in the dimensions in the view – below that dimension, the detail and the Include expression return the same value – above the limit, the Include expression works like “Fixed” and returns the value of the Fixed dimension
In this example "Ship Mode" is the Dimension used as the base
{ INCLUDE [Ship Mode]:avg([Sales])}
Below Ship Mode, the Tableau Average and Include Return the same value – the average is the total sales divided by the record count at the detail level (Region-Ship mode – Segment) – Include returns the same values at the level
When evaluated at the level of the Include statement (Ship Mode) the individual row level values are the same as the Tableau calculated averages – the sub-total of the Include is the average of the 4 regions where the Tableau values are the average value of all records in the Region
At the Region level – above the level of Include we see only 4 rows – the Include statement averages are the average of the 4 Ship Modes in the regions where the Tableau average is the average of all the records in the region.
In contrast, the Exclude function performs the aggregation at the next level above the dimension level in the Exclude statement
{ EXCLUDE [State/Province]:sum([Sales])}
Will aggregate the Sales at the next highest level above State in the map – the Region
For the advanced user, a more detailed explanation can be found at the Link to Include and Exclude on my blog
In the previous steps, we have looked at filters that affect the structure of the data table. In Step 8 we look at Measure Filters that operate on the Values in the data table. They can be applied at individual record level in the underlying table for the worksheet or at the aggregate level:
Using Superstore data we first find the number of orders that have an individual record (think row on the invoice) that have Sales $ greater than $10K then we find the total number of Orders with Sales $ greater than 10K – There will be more Orders with TOTALS greater than $10K than individual rows greater than $10k
Create the viz and drag Sales to the Filter Shelf – and a window opens – Selecting the “All Values” option applies the filter at the record level in the underlying (detail) level of the data table. Any other options apply to the aggregate as created by the Dimensions in the viz – The default aggregation is Sum() but many others are available
Here the All Values option is selected and the Sales row level is filtered to greater than 10,000 – the result is 5 orders have individual rows that meet the criteria
In the same viz, if we filter at the aggregate level for orders with a total value greater than 10,000 there are more than 5000 orders
The 9 Step – Grand Totals and Sub-Totals is a broad topic
Here we cover the basics which are cool in themself.
Totals and subtotals are calculated in a separate module in Tableau. There the individual detailed records in the underlying data table are aggregated to get the totals – that differs from spreadsheet calculators where columns or rows are summed from the sheet itself.
The Grand Totals function can be reached through “Analytics” on the top ribbon or (my fave) can be Dragged and Dropped on the canvas from the Analytics tab in the Date Frame
The default aggregation is Sum()
But that can be easily changed by opening the Measure green pill, picking Total Using, and then the option you want – The aggregation will be used on the Totals (Rows and columns) – the table cell values will be aggregated by the selection made when they were brought to the viz.
Sub-totals can be applied in the same way- Drag and drop the total to the Subtotals Icon
And Subtotals are applied at the Segment level:
Grand totals and subtotals can get very complicated – nested subtotals, totaling table calculations, and custom totals are a few – When you are ready, I encourage you to read Link to GT and Sub T which provide detailed use case examples
Table Calculations are evaluated in the 10th and last step of the Order of Operation – They are an extensive topic that will be covered in detail as a “Special Calculation” in a later section.
As a newbie Table Calculations were in my comfort zone. They are similar in concept to the way calculations work on spreadsheets and they are primarily executed on the table that you see in the viz –
With Table Calculations, we talk about Scope and Direction, and position within the table. Scope refers to how much of the table is used in the calculation, Direction – Across or Down – is the direction in the calculation is executed in the table (In Excel it is equivalent to the direction you copy and paste a formula) and position is a table is the cell location with respect the other cells around it (similar to Offset in Excel).
In this basic example, the total sales is each Subcategory are added to the Running total going down the table
To get you started, Tableau has a group of commonly used Table Calculations that can be applied directly from a dropdown menu
Table calculations are also used to navigate the table – there are 5 functions – First, Last, Index, Previous Value, and Lookup which can be used to return a value from a relative position in the table.
For example:
Lookup(sum(sales),-1)
Will look in the column “Sum(sales)” and look back 1 cell (upward) and return the value from that cell.
Likewise
Lookup(sum(sales),First())
Will return the first value from the sum(sales) column
The best way to learn table calculations is to use them – practice and see what they return. With practice, you will be able to write custom and nested table calculations and use them in combinations. When you are ready see the post at Link to Table Calculations where I go into much more depth and include use case examples using advanced table calculations.
