What does it mean?!
LOD stands for Level of Detail expression in Tableau. There are three types of LOD:
- Fixed
- Include
- Exclude
In this blog, I will just be focusing on the first of these, the Fixed LOD.
Why do I need it?
LODs are extremely useful as they allow us to compute aggregations that are not at the level of detail of our visualisation. Without LODs, Tableau computes all aggregations in relation to the dimensions that are in the view.
Using the Sample – Superstore dataset that comes with Tableau, we can explore what this means.
After connecting to Superstore, create an empty sheet and drag the ‘Sales’ measure onto the ‘Text’ icon in the Marks card. The sheet will show the total sum of sales for all the sales in the dataset.
Next, drag the Order ID dimension onto the rows shelf, this aggregation for sales is now broken up by all the Orders. We can continue to break up the aggregation further and gain greater granularity by dragging more dimensions onto the shelf such as Product name.
As we can see in the examples above, the level of detail of the aggregation is based on whatever dimensions are placed on the columns, rows, the marks card or even in the view. The more dimensions we utilise, the more granular the view of the data will be.
However, if we want to calculate an aggregation at a particular level of detail, irrespective of the dimensions in view, then we need to use an LOD.
What is the structure of an LOD?
An LOD has the following structure or syntax:
{ FIXED <dimension declaration> : <aggregate expression> }
In English, please?
This structure makes a bit more sense when you translate this Tableau-talk into plain English. Coach Andy’s blog on putting a fixed LOD into an English sentence is very useful for learning about this.
As Andy explains, a fixed LOD expression can be translated as:
For each dimension, compute this aggregate expression.
With this expression, we can specify zero or more dimensions to compute the aggregate by. If we do not specify any dimension, then Tableau fixes the aggregation on the whole dataset.
As an example, when using the Superstore dataset, we could use the following fixed expression in a calculated field:
{Fixed : SUM(Sales) }
This LOD would compute the total sum of all sales in the Superstore dataset, Tableau would return this grand total no matter if there were any other dimensions in the view.
Show me how this works!
So, if we return to the Superstore workbook we created, we can put our fixed LOD into action and watch it work its magic.
If we wanted to calculate the total amount that each customer has spent over multiple orders, whilst keeping every Order ID for the customer in the view then we would need to use an LOD. Create a calculated field and type in the following:
{ FIXED [Customer Name] : SUM(Sales) }
In English: For each Customer, compute the total sales.
After creating the calculation, drag this onto the Text icon in the Marks card. Our LOD aggregation will return the total amount spent by each customer.
On the view below we can see that when customer is in the view our LOD returns the same value as the regular aggregation for sum of sales as both are computing on the customer level.
However, when we drag Order ID into the view, the fixed LOD aggregation remains at the customer level but the Sales aggregation is broken up. The sales aggregation has been changed due to the additional dimension added to our view. The fixed LOD continues to compute solely at the customer level.
In this example the LOD expression has allowed us greater control over the level of granularity we want to compute.
