Tableau LOD Expressions
Tableau LOD Expressions are probably the hardest topic to fully understand, or at least it was for me when it first came out. To be honest I dodge this explanation as much as possible haha, probably why it took me so long to write a blog about it. It’s just one of those topics that are very hard to explain, you just have to show someone what it does then the light bulb turns on. So if you are reading this and at the end, you’re still confused, please watch the video. I promise it’s a much better way to learn this concept. Also, make sure you download the 1 page PDF that explains each one with charts so you can always have something to reference when dealing with them.
FREE DOWNLOAD: Don't leave empty handed, download the Tableau {LOD : Expressions} PDF to take with you. All you need to know explained simply on a 1 page PDF.
I would also recommend after reading this blog to go and check out Tableau’s blog and examples, they do a really good job at explaining it also. Before we start anything we first need to talk about granularity and aggregation.
Granularity & Aggregation
Granularity and aggregation work opposite of each other. In Tableau, when you bring dimensions and measure to the view, the measures are aggregated by whatever dimensions is on the view. As you remove and add more dimensions you are decreasing and increasing the granularity. The more dimensions/details on the view, the more granular it is.
If you look at the picture below, you can see how they work opposite of each other. As you aggregate things up more the less granular it is, and as you increase granularity you decrease your aggregation because you are drilling down into your data more which is getting you closer to the lowest level of detail. As you keep adding more dimension you will eventually get to the row level of your data, at that point you will have no aggregation and your view will be the lowest level possible.
In this example, we have 3 dimensions out on the view so your viz level of detail is that line shown in the photo below. Every measure on your viz will be aggregated at that level which means it will be aggregated by the combination of those 3 dimensions. Now with Tableau’s level of details expressions you can calculate a specific measure at a different level of detail than what your viz is on. That means you can, for example, calculate the sum of sales by dimension 1 and dimension 2 instead of all 3 dimensions that your viz is being calculated on. This allows you a lot of flexibility because you can calculate measures at different levels of detail without changing your viz.
There are 3 Tableau LOD Expressions, Exclude, Include and Fixed. The syntax for all 3 are very similar.
- You first start with an open curly bracket “{“.
- Type either Exclude, Include or Fixed
- Followed by zero or more dimensions with a comma separating them
- Then add a colon “:”
- Now you put an aggregate (Sum(), AVG(), MIN(), etc.…)
- Finally, close it with a closed curly bracket “}”
Exclude Level of Detail Expression
{ Exclude Dimension1, Dimesion2… : Aggregate }
Exclude calculates the measure at a higher level of detail then what is on your view. The Exclude LOD is dependent on what’s on your view so you can only exclude dimension’s that are currently in your visualization.
For example, if you have 2 dimensions on your view, let’s say category and sub-category. You want your viz to aggregate the sum of sales by these 2 dimensions but you also want to know what the sum of sales is for the entire category also. Then, in this case, you can exclude the sub-category from your calculation so that this measure will ignore sub-category and aggregate up to category. You can then show the sum of sales for Category which is calculated at a different level of detail then what is on your view. The only other way to get this number is to remove sub-category from your view so the sum of sales is calculated by just category.
Include Level of Detail Expression
{ Include Dimension1, Dimesion2… : Aggregate }
Tableau’s Include LOD is used when you want to calculate a measure at a lower level of detail compared to the viz’s level of detail. Your calculation will look at the dimensions on your view plus any additional dimensions you put in your statement.
In this example let’s say we start with category and sub-category again. Now there is another field in the data called manufacturer which is the individual manufacturer that roll up to the sub-category. We want to see what the average sale is by the manufacturer so we have to sum the sales by manufacturer then average all of those numbers. We can do this by using the Include statement as I did below.
Fixed Level of Detail Expression
{ Fixed Dimension1, Dimesion2… : Aggregate } or {Aggregate}
Tableau’s fixed lod function calculates your measure independent of what’s on your view. The Fixed function does not look at the view at all; it will calculate your measure depending on what dimensions you put in your equation and nothing else.
To me, the Fixed function is the most powerful one out of the three. Fixed can do everything the other 2 can do plus a lot more. In this example, I will show you 2 ways on how you can use the Fixed LOD. The first example is we are trying to find the first order date for each manufacturer. Each manufacturer has many order dates from many orders but we want a column that will just capture the very first order date for every manufacturer.
The next example is looking at the largest sale for each manufacturer. Once again each manufacturer has a lot of different orders but we want a column that just has their highest order. Once we have that column we can see what their largest order is and also what the sum of all their orders are in one view.
As you can see Tableau LOD expressions can do things that seem almost like magic. After you start using them you will be using LOD’s to fix all your problems. If a calculation is giving you a hard time, pull out a LOD and I bet you that will save the day.
FREE DOWNLOAD: Don't leave empty handed, download the Tableau {LOD : Expressions} PDF to take with you. All you need to know explained simply on a 1 page PDF.
