Jim Dehner

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

The Order of Operation is the single most important concept you need to understand during your journey to learn Tableau.

So what is the Order of Operation in the first place? Simply stated it is the sequence Tableau follows each time you create a new worksheet.  I like to use the 10-step model viewed in 3 groups:

The first 2 steps operate at the workbook level and can improve the performance of the workbook.  The remaining steps are executed with each new worksheet.  Steps 3-5 create the structure for the data table for the sheet while the remaining 5 steps (6-10) load the table with data, perform calculations and create the viz.

Here we will look at how specific steps affect simple and more complex calculations.

#### Steps 1-2 Data Source and Extract Filters

These filters reduce the workbook's data when files are uploaded into Tableau.  Eliminating unneeded data will make to workbook perform better.  They are very easy to use and applied on the Data Source tab.  In their most basic form, it is similar to adding a dimension filter.  From the data source tab select ADD and the dimension to filter and the values to keep.  In this example keeping only furniture reduced the number of records from 10K to 2200 –

Now for the more advanced version using a conditional expression –  conditions that can be determined at the time the data is loaded, it can be used to create a dynamic filter –

For this example I only want to include customers whose total sales are greater than 10K.  Since the expression is calculated as the data is loaded the filter is applied dynamically each time the data is updated

The LOD is:

#### { FIXED [Customer Name]: sum([Sales])>10000 }

The LOD is selected from the dropdown and set to True – as a result the number of customers in the dataset is reduced from 800 to 21 that have sales greater than 10k with a total record count of 418.

#### Steps 3-5 Context Filters, Fixed LOD, Sets, Top N and Dimension Filters

For new and experienced users alike, when and how to apply Context filters causes most of their errors.  If you get nothing else from this post just remember this – a dimension in Context is filtered before the Fixed LOD is calculated, Sets are created or the Top N is determined.  Dimensions not in Context will be filtered after.

Ok not so hard – let's look at some basic examples:

In this first example, the Sales at the Category and Year level are fixed with this:

#### { FIXED [Category],year([Order Date]):sum([Sales])}

The total sales in each category are fixed –  even when we apply a filter on the subcategory as a dimension filter (blue pill) the value remains unchanged (Ie the filter is applied to the subcategory after the LOD is calculated.

When Subcategory is placed in Context (Gray pill) the filter is applied before the LOD is calculated so the total for each category now reflects the filtering

Something similar happens when we use Top N – I created a set on Subcategory using the Top N option

Applying a filter to a subcategory and the filter is a blue pill (dimension filter)  the filter is applied after the Top N are determined only 2 subcategories are in the set

If I place the filter in Context – 5 subcategories are returned and they are the Top 5 of the remaining 14 after the 3 were filtered out

So the rule still applies – Dimensions in Context are applied before Fixed LODs, Set or Top N are applied and Dimensions filters applied NOT in context do not affect those values.

But what happens when you need to filter applied to a Fixed LOD and to a Top N? – That leads to the advanced case because Fixed LODs and Top N are both determined in the same step of the Order of Operations.   –  In the example, we try to apply a filter for this LOD

#### { FIXED [Sub-Category]:sum([Profit])}>5000

And also take a Top N  based on Sales but we can't get both filters to work properly together

We have to go back to the Order of Operation and find an alternate way to find the top-performing subcategories that also meet the min profit goal.  That can be accomplished by ranking the subcategories using a table calculation

#### RANK_UNIQUE(sum( { FIXED [Sub-Category]:sum([Sales])} ),'desc')

And then using a Boolean filter based on a rank less than a parameter value

#### [2 rank based on sales]<=[Select N for Top N]

Combined they now return this

#### Steps 6-10 in the Order of Operation

The remaining steps fill the worksheet data table with data, perform some calculations and create the viz.  Here we will only focus on steps that involve calculations.

#### Step 7 Include and Exclude LODs

Include and exclude are LODs that operate on values in the data table.  They are used in about 20% of use cases and often lead to confusion.

The Include statement can be used to set the upper limit for the aggregation in a LOD – it is easier to understand in this example:

#### { INCLUDE [State/Province]:AVG([Sales])}

At the "Region" level and above, the statement calculates the average of the within each region and then aggregates that total

At lower levels in the viz include returns the same averages as those for the individual records – the automatic totals will reflect the regions averages

Exclude aggregates values at the next hirer level dimension in the viz – Again an example is easier to see using this:

#### { EXCLUDE [State/Province]:AVG([Sales])}

Excluding the State will return the Region averages at the region level in the viz but note it can not be used in Grand Totals

At levels below region Exclude returns the Region's total

Hope that clears up how to use Include and Exclude – an interesting use case is to create a bullet chart

#### Grand Totals

Basic Grand Totals and Subtotals are very easy to use – just remember that Tableau calculates the values in a separate module and based on the filtered data table for the worksheet – for a complete explanation see Totals and subtotals

You can simply drag and drop Grand Totals from the analytics tab

The default aggregation is Automatic (that is based on your custom calculation or sum())  but you can adjust that as needed

Sometimes the built-in aggregations don't result in what you expected – in most of those cases, you can use a custom expression to create totals.  In this use case, the percent of total category is calculated for each subcategory at the row level but the category percent to overall total sales is presented at the subtotal levels

It is done with this formula

#### Sum({ FIXED Year([Order Date]),[Category]:sum([Sales])}) end

The formula takes advantage of the fact the subtotal and grand total "rows" are calculated in a separate module –  the first condition is to test if the min subcategory is the same as the max (that is true for each of the line item row but not in the subtotal for the category – when that is found the "Then" clause is explicitly formula to apply in that row – the Else clause is just the formula use elsewhere.

The same approach can be used to sub-total multiple layers in a drill-down hierarchy  – see Multi-layer custom totals

#### Table Calculations

Table calculations are last in the order of operation and, in their basic form are easy to apply and understand – Most training programs introduce table calculations with a percent of total using a "Quick Table Calculation"

and then change the scope and direction using the Editor:

Easy enough, now let's move to a couple of more advanced calculations – the firsts nests 2 table calculations – Building upon the percent to total by adding a running total by category:

This is the basic percent to total calculation calculated down the table

#### SUM([Sales]) / TOTAL(SUM([Sales]))

That formula can be nested in another calculation that does a window sum on a running basis by category using this :

#### window_sum([5 percent of total ],FIRST(),0)

the optional start and stop say begin the total at the first record ( First())  and sub through the current (0).

and you can create your own

Early on you learned that you could not nest table calculations in LODs and that is true (you should understand why now)  – but you can nest LODs in table calculations – In this use case we calculate the rank (a table calculation) of the segment, subcategory annual sales (an LOD):

First the LOD

#### { FIXED year([Order Date]),[Segment],[Sub-Category]:sum([Sales])}

now the Rank table calculation – not the LOD needs to be aggregated with Sum():

#### RANK_UNIQUE(sum([6 fixed sales year seg sub cat]),'desc')

In text chart form with some formatting applied the result could look like this

I hope you better understand how you can leverage the Order of Operation when you need advanced calculations.  The best way to learn now is to practice – make up your own examples or look for ways in your next example.

Enjoy

Jim