Fixed, Include and Exclude

Tableau 9.0 introduced many new features to play with, but undoubtedly the most exciting was the Level of Detail (LOD) expressions, namely Fixed, Include and Exclude.  In Tableau 8 and earlier, expressions are calculated according to the level of aggregation in the visualisation, known as the ‘Level of Detail’.  With the new LOD expressions, the user now has the ability to go beyond this and aggregate using dimensions that are not present in the visualisation.

These new expressions introduce a simple new syntax:

{FIXED [Dimension], [Dimension] … : Aggregate expression}

{INCLUDE [Dimension], [Dimension] …  : Aggregate expression}

{EXCLUDE [Dimension], [Dimension] …  : Aggregate expression}

LOD calculations work by constructing subqueries in the background using Tableau’s internal query language to perform multi-level aggregations, where you are effectively pre-aggregating your data in a subquery before performing your primary aggregation in your main query, you can see how this works if you examine your Tableau logs.  Although these new functions have been robustly tested, when combined with existing complex custom SQL queries, it’s possible to produce some unusual errors and checking your log files is a great way to debug these.

So the first question to answer is what do these new expressions actually do?  Let’s take fixed first.  Fixed calculates the aggregation at the level of detail specified in the expression regardless of what is present in the view.

{FIXED [Customer] : sum([Sales])}

The above expression would calculate the sum of sales per customer, irrespective of what other dimensions are present in your visualisation.  One of the interesting aspects of the fixed statement, is at what point it gets calculated by Tableau.  It is the only measure that will get calculated before dimension filters are applied, which gives it some powerful functionality, by contrast the include and exclude functions are calculated immediately after dimension filters are applied.

For example I could write the following:

Sum([Sales])/Attr({FIXED : sum([Sales])})

Here I have excluded the dimension in my fixed statement, making it calculate over the entire table giving a single value i.e. total sales.  Hence the above expression returns a ratio between Sales and total sales.  If I had customers as a dimension in my view, I could then see each customer’s sales as a ratio against total sales, but the key point about fixed is that if I filter customers in some way in my view it does not affect my fixed statement as it is calculated before dimension filters are applied.  If you had a filter in your view that you do want to be applied before you fixed expression you can simply promote it to a context filter. This is just one small example of how fixed can be useful, but it has many more applications.

Let’s look at include and exclude next, so what exactly do they do?

• Include calculates the aggregation at the level of detail in the view, and including the dimension or dimensions in your expressions, even if they are not in the view.
• Exclude does the opposite, it calculate the aggregation at the level of detail in the view, but excludes the specified dimension or dimensions, even if they are in the view.

Let’s look at a couple of examples.  A common business question is finding the average of a sum, such as “What is the average total sales generated by each member of the sales team in a week?”.  Prior to Tableau 9, you would have to aggregate your data in some way before bringing it into a sheet, almost certainly using a custom SQL query.  However now you can simply write this:

{INCLUDE [Employee], [Week]  : sum([Sales])}

This expression will sum up the sales in the view, first aggregating by [Employee], and then by [Week], giving the sum of the sales per employee, per week and this can now be dragged into your shelf and averaged to give you the answer you need without having to do resort to any of the complex methods you would have had to previously.

Exclude is the opposite of include, meaning that it will perform your aggregation at the level of detail in the view, excluding the specified dimension or dimensions.  This makes it extremely useful for comparing against totals or overall averages.  For example, let’s consider a chain of stores that wants to compare total average customer spend, to average customer spend in each store.  To calculate this they need to find out the sum of each customers sales, but given that they need to have stores in the view as well and customers can spend in multiple stores, trying to sum sales normally will be incorrect as it will aggregate at the wrong level of detail.  Instead you can write:

{EXCLUDE [Store]  : sum([Sales])} 

This calculation tells Tableau to ignore the [Store] dimension, even though it is in the view, allowing you to attain the correct total. 

To conclude, LOD calculations are extremely powerful tools in Tableau 9 that allow you to answer complex questions in a couple of short steps, the examples above barely scratch the surface of the kind of things you can do with these new calculations.  When you also realise that you can nest these functions, and you can even include IF statements inside them, it is easy to see that there is a huge amount you can achieve with a bit of thought.

You can find more interesting blog posts at http://blog.bipb.com/.