'Level of Detail' expressions: the power of Tableau 9.x!

Monday, September 28, 2015 Unknown

Did you ever had a need to do calculations in Tableau where the calculation is not solely based on the details available in the visualizations? For example: it is quite simple to return for every state the ratio of its sum of sales to the overall sum of sales (of the world). Suppose we want to focus on the Europian market which can be done by using a filter for the Market field. Using this filter would lead to a recalculation of the ratio's, so we would get the percent of total according to the Europian market. Without a Level of Detail expressions, this problem would be unsolvable.

A LOD expression usually looks as follows:
{Scoping keyword [field1]: agg(field2)}
where the scoping keyword can be FIXED, INCLUDE or EXCLUDE. We will explain these three different types of LOD expressions in the examples below. We made these examples in Tableau using the Sample Store dataset.

   1.  FIXED
Suppose you want to compare your customers according to the year they made their first purchase. This analysis is known as cohort analysis. Thus, for every customer you would like to have the acquisition date. This can be done by creating a calculated field displayed in the figure 'FIXED' above.

The scoping keyword is FIXED which computes a value using the specified dimensions, independently to the dimensions in the view. This calculated field returns for each customer the first order date regardless what other dimensions are in the view.

    2.  INCLUDE

One order can contain multiple items, and these items are separated into different rows. Using aggregations at row level, will lead to information about the items in the orders. Thus, if you are interested in the visualizations at order level, you will need an LOD expression as in the view 'INCLUDE' above.

INCLUDE leads to an aggregation at the level of detail specified in the view and the dimensions included in the calculated field. The view above calculates the highest sales for a certain order in each state, while the field Order ID isn't included in the view.

     3. EXCLUDE

For every month, you are interested in the total shipping cost as well as in the total shipping cost for each region. Without any use of LOD expressions, you would get the first figure in the 'EXCLUDE' view. This returns for every month and for each region the total sum of shipping cost, and the columns at the right return the total of these sums.

Now it's possible to get another view using a LOD expressions as displayed in this view. By using the EXCLUDE scoping keyword, we're able to leave a dimension out of the aggregation, while this dimension is used in the view. In the 'EXCLUDE' view, the region dimension is used in the view, but the aggregation used for the color legend doesn't take region into account.

A little note to the LOD expressions: Google Bigquery doesn’t support the LOD expressions yet.