LOD (level of detail) calculations allow you to make calculations at a specific level of granularity. They enable more precise control over what level of detail the data is aggregated over. This is something which standard aggregations in Tableau Prep fail to do.
There are three types of LODs: Fixed, Exclude and Include. In this blog we will only discuss the fixed kind.
Fixed LOD
A fixed LOD does exactly what it says on the tin and is formed as follows in Tableau Prep:
Consider the prep flow in figure 1 on the subject of tax. In a clean step if you right click on a field and navigate to 'Create Calculated Field' and then 'FIXED LOD' you will reach this particular view. A key tip when working with LODs is work from the inside out. In this case we want to compute the maximum row number and group (or fix) by the staff ID. In English terms this means for each staff ID what is the highest number of rows.
We can see the result of performing this LOD in figure 2.
Self-Join Aggregations
We will now look at the same process using the self-join method. Self joining involves a flow joining back onto itself to preserve fields which would otherwise be lost when performing an aggregation. Consider the flow in figure 3:
An aggregation over the max row number and grouping over the ID has been performed and then this flow has been joined back onto itself via an inner join to yield the above result. The join clause in this case is 'StaffID' which has a 1:1 relationship with itself hence the reason for this type of join. If you look at the number of fields and rows in both examples you will see that they are the same (15 fields and 999 rows) and that the values in the table are identical.
The exact technique you will use will depend heavily on the nature of the data and how it needs to be prepared and in this case both methods were fairly simple to implement. Aggregations and self joins are usually easier to understand so may be a more appealing method if you are less familiar with LOD's but be mindful that using an incorrect join type could have serious consequences such as an explosion of rows. Joins are also quite intensive from a computing perspective so this is something to bear in mind for larger data sets. In general, a fixed LOD is probably better.