LODs: Level of Detail Calculations. The calculation monster under the Tableau bed. Marred with confusing complexity. Nah! Let's talk about it, it isn't that bad I promise.
Syntax
LODS are written in a particular way in the calculation editor.
Anything held within {curly brackets} (AKA braces or moustachios) is a Level Of Detail calculation.
This can be as simple as
{ SUM([Sales]) }
To nested LODs
{ SUM(
INT(DATETRUNC('quarter', [Order Date]) = { MAX(DATETRUNC('quarter', [Order Date])) }) * [Quantity]
)}
And beyond.
But what do they actually do?
LODs do calculations over the whole dataset instead of just at a row level like a regular calculation.
Imagine a dataset of puppies, you have fields for Puppy Name, Date Recorded, Height, Weight and you made a calculation [Height] * [Weight] Tableau will go row by row for each puppy and multiply the height and weight.
LODs do not work on a row by row level. When you write MAX([Date Recorded]) it returns the max date for each row, which is just the date of that record.
When you write {MAX([Date Recorded])} then you would get back whatever the latest date value from the entire date field is for every row.
The Three LOD Functions
- FIXED
- INCLUDE
- EXCLUDE
Along with the curly brackets to make Level of Detail calculations Tableau also has 3 functions we can use specifically for LODs.
FIXED
Always use only this
The fixed function instructs tableau to break down a field by always and only the field you specify. This will therefore ignore what you may have on the view and in filters etc.
Superstore Example
{FIXED [Region] : SUM([Sales])}
This LOD means sales is always being broken down by Region and nothing else, regardless of what is on the view. So when put on a chart with region and state you can see it is not breaking down sales by state, only region.
INCLUDE
Always use at least this (plus anything else on the view)
The include function instructs tableau to always use the field you specify. It will also include anything else on the view. This is useful when you want something broken down by a field you do not want on the view.
Superstore Example
{ INCLUDE [Customer Name] : SUM([Sales])}
This LOD means sales is always broken down by customer. So when put on a chart with region and made an average you are seeing the average sales per customer per region without having customer name on the view itself.
EXCLUDE
Always ignore this (but use anything else on the view)
The exclude function instructs tableau not to use the field you specify. It will include anything else on the view. This is useful when you want a field on the view but you don't want the want the data to be broken down by that field.
Superstore Example
{EXCLUDE [City] : SUM([Sales])}
This LOD means when city is on the view, sales numbers will not be effected. So when put on a chart with state and city every city is given the same value for sales, and that value is the sum of sales for the state as that is the only field by which tableau is breaking down.
Summary
I hope you found this useful. If you're not sure when you would use these in your calculations I recommend keeping the logic of these functions in mind, they are often the answer to your calculations not giving you what you want.
