Often, over the course of The Data School, I would hear the term ‘LOD’ being thrown around and I would think to myself ‘what exactly are these, and what do they do?’ – I’d been intending to look into them but it kept being pushed aside.
Well this week, as a result of the Tableau teachings of Carl Allchin, I need wonder no more.
So, what is it?
Well, LOD stands for ‘Level of Detail’ – and it is this important phrase that truly needs to be understood before approaching these useful calculations.
Levels of detail can be thought of as hierarchical and It is important to be aware of these hierarchies in the data that you are analysing – because, more often than not, they will exist.
For example, think in terms of date:
1 Year, has
12 months, and
365 days
The level of detail is the point in the hierarchy by which Tableau will aggregate data when a dimension is dragged onto a shelf or card in the visualisation pane.
Let’s look at an example:
Here by simply dragging ‘Sales’ (averaged) onto Text in the visualisation pane, Tableau has taken the entire sales column of the data set, summed it and divided it by the entire 9,994 rows.
But then if the highest level of the product hierarchy ‘Category’ is added to the columns shelf:
Tableau will split the data by the category first and then average them individually. As seen in the table above, there are 2,121 rows in the furniture category, the values for sales have been averaged separately to those outside of that category.
This system will continue, as lower levels of the hierarchy are pulled into the visualisation pane, providing more granularity, but fixing the aggregation to that lowest point.
But what if you want to aggregate to a different level of hierarchy than those in your visualisation pane?
This is where LOD calculations come into play. These allow the user to specify the level of detail/ hierarchy that Tableau should aggregate at.
In the example shown below two simple LOD calculations have been applied to help iterate this concept:
Here the first of the three bar charts displays the top 5 average sales per product within each subcategory. The granularity here being the expected, due to the dimensions ‘sub-category’ and ‘product name’ being in the visualisation pane on row – ‘product name’ is the lowest in the hierarchy, therefore, the averaging is at this level.
However, for the next two charts, an LOD calculation has been applied for each to set the level at which the averaging is taking place. For the middle chart, the average per ‘sub-category’ is displayed and for the right chart the average per ‘category’ is displayed – ‘category’ has also been applied to colour to help see this.
Now, due to LOD calculations, averages at higher levels in the hierarchy are displayed, allowing a point of reference. A simple but potentially effective use, and only one of many applications for this type of calculation.