The ability to use LOD (Level of Detail Calculations) in Tableau Prep is one of the most anticipated innovations in this tool. Until now, they required some skill and cleverness. Now, from version 2019.3 onwards, they are available to everyone.
What are LOD calculations
Level of Detail Calculations, also known as LOD, are calculations of the level of detail. In Tableau Desktop this means showing data in a different level of detail than our visualization. By comparing sales in the provinces, we compare them to the results of the macroregion. Or the opposite. We reach for the results of the best and the worst district in each province. A special feature of LOD is that although they sum up or average the data, Tableau does not treat them as aggregates. What does that mean?
In Tableau we have calculations at the level of individual values from the source table or at the level of aggregation. Let’s take a table where in each row we have the order values of one customer, and the columns are consecutive months. Comparing two consecutive months we can do it at the level of a row – by placing orders for each client separately. We can also compare aggregated values – e.g. sums from columns for each month. As a rule, these types of calculations cannot be mixed up in Tableau. LOD calculations are an exception. How is it happening?
Suppose that we have data on profits throughout Poland, presented on a regional map. We want to add to the analysis the best and the worst county in each province and the national average. In the LOD calculation we will determine the level of analysis (counties/ Poland) and type of aggregation (MIN/ MAX/ AVG). Tableau will assign the calculated result to each row of data. Each of them will be accompanied by information on profit and sales for the best and worst county and an average for the whole country.
Technically, we have three functions that allow it. The FIXED function determines the level of analysis, no matter what we have in the chart. The INCLUDE function attaches additional levels of analysis to the visualization and the EXCLUDE function removes selected dimensions from it. Their characteristic feature is their specific syntax with braces, e.g.:
{FIXED [Region]: AVG ([Sales])}
LOD in Tableau Prep Builder
There is no visualization in Tableau Prep. Only a table with data rows is available. Hence the simple conclusion that only the FIXED function can be used here. And this one aggregates data at a given level. If someone has already worked with Tableau Prep, they know it’s not new. The Aggregate step makes it possible for a long time. So what have we gained from LOD? The data can be easily combined in the same table at different aggregation levels. A simple aggregation “rolls up” the table. Instead of e.g. 250 lines, after calculating the provincial averages we will get 16 lines. Until recently, the LOD effect was achieved by combining the aggregated table with the original one as below.
Today the additional Aggregate step and the additional Join step can be replaced by a simple and familiar Tableau Desktop calculation. That’s not everything. In the near future, Tableau Prep Builder will also offer a special simplified interface for creating detail level calculations. Additionally, it will be used to obtain the RANK function in Prep.
For now, however, it is worth remembering the old method of obtaining LOD calculations in Tableau Prep Builder. Why? The program so far supports only the simplest of their characters. We will still have to wait for the possibility of nesting a complicated calculation. But we can already speed things up a lot.
