1. INCLUDE and EXCLUDE LOD expressions
Hi again, I hope you're enjoying the course so far.
Let’s continue our exploration of Level of Detail expressions.
2. LOD expressions - recap
As a reminder, LOD expressions are a family of three powerful formulas that allow us to calculate at a different level of detail than the data in the visualization.
In the previous video and exercises we’ve zoomed on FIXED LOD expressions and in this video we will explore the two other LOD expressions, INCLUDE and EXCLUDE.
These expressions allow us to calculate at a higher or lower level of detail than is present in Tableau’s canvas.
3. INCLUDE LOD syntax
Let’s begin with the INCLUDE LOD expression.
It allows you to calculate at a finer level of detail in the database and then re-aggregate and show it at a coarser level of detail in your visualization.
Unlike FIXED LOD expressions, INCLUDE is sensitive to adding or removing dimensions in the view, as doing this will change the calculation.
4. INCLUDE LOD example
Let’s consider the following example: in our data each row represents one order.
If we wanted to calculate the profit we’ve made on each order and customer, we can include these dimensions in the view. But with many customers and orders, this can be a huge list.
If we wanted to calculate an average profit per order within the Product category, we could just remove all other dimensions.
But what if we wanted to calculate it on Customer level, without adding the customer ID to the canvas?
We can achieve that, with an INCLUDE LOD expression, including customer dimension in the calculation. We first sum the profits per customer and then average them by wrapping our expressions in an average aggregation either directly in the calculation editor or in the view.
5. EXCLUDE LOD syntax
Similarly to INCLUDE, we can also remove some of the detail from the view, using the EXCLUDE function, in order to calculate at a higher level of detail than the one present in the view.
Just like with the INCLUDE expression, EXCLUDE is sensitive to adding or removing dimensions in the view, as doing this will change the calculation.
6. EXCLUDE LOD example
A classic use case for EXCLUDE LOD expressions is generating totals or subtotals reusable in other calculations.
In this example we want to obtain the subtotal of Profits per product category.
We could apply subtotals from the Analysis tab, but we cannot use them in any further calculation.
With EXCLUDE LOD expressions, we can calculate these subtotals ourselves, by EXCLUDING Product ID from the sum of Profit calculation.
Finally, we could divide the Sum of Profit by this calculation and obtain % of Profit Contribution, per product.
7. FIXED vs. INCLUDE vs. EXCLUDE?
To recapitulate, FIXED LOD expressions calculate at a specified level of detail, and they will always give the same result regardless of what’s in the visualization.
However, INCLUDE and EXCLUDE expressions, calculating respectively at a lower and higher level of detail than present in the canvas, will render a different calculation result when dimensions are added or removed from the canvas.
8. Order of operations
Before we head off to the exercises, let’s have a quick look at Tableau’s order of operations as depicted on the slide.
It is the order in which Tableau executes the filters in the calculations, starting with the extract filters, and then going from top to bottom.
For LOD expressions, it is important to notice that FIXED expressions are performed prior to any dimension filters, as they are calculated at a specified level of detail.
INCLUDE and EXCLUDE expressions, however, are performed only after the eventual dimension filters are applied.
So if you run into unexpected results of calculations involving filters and LOD expressions, it may be useful to deepen yourself into the topics of operations.
I recommend checking out Tableau’s knowledge site, accessible via the below link.
9. Let's practice!
Let’s see all this in practice.