28 Apr 2017

A Quick Guide to Tableau Level of Detail Expressions

Nicolas Voirin

Have you ever received the following error message when creating a calculated field in Tableau: "Cannot mix aggregate and non-aggregate arguments with this function"?

Don't be frustrated. From version 9.0 onwards, users can leverage Level of Detail (LOD) Expressions to avoid this error.

Before these expressions were supported in Tableau, it was not possible to create calculations at a level of detail other than the view level. Let's look at a business example. You want to compare the quantity of product sold per customer to the average quantity sold per customer in a country. Before LODs were available, you might attempt it with an expression similar to Figure 1 below and receive an error message. Thanks to LODs, you can now use the syntax shown in Figure 2 and get your answers exactly.

Figure 1

Figure 2

LOD Types

There are three types of LODs: Fixed, Include and Exclude. It is useful to be aware of what each can do:

  • Fixed: calculates the aggregation at the level of detail specified in the calculated field regardless of any dimensions in the view. e.g.:

{FIXED [ Region ]: Avg ( [Sales] )}

This expression calculates the average sales per Region, regardless what other dimensions are in the view.

  • Include: calculates the aggregation at the level of detail specified by the dimensions in the view and the dimensions included in the calculated field. This will potentially result in the calculation of the aggregation at a lower level of detail than the view. e.g.:

{INCLUDE [ Product Name ]: Avg ( [Sales] )}

This expression calculates the average sales at the level of detail defined in the view, but includes the dimension Product Name, even if Product Name is not in the view.

  • Exclude: calculates the aggregation at the level of detail specified by the dimensions in the view, excluding any listed in the calculated field. This will potentially result in the calculation of the aggregation at a higher level of detail than the view. e.g.:

    {EXCLUDE [ Category ]: Avg ( [Sales] )}

This expression calculates the average sales at the level of detail defined in the view but does not include Category (the dimension) as part of the level of detail, even if Category is in the view.

Use Cases

So now we know the different types of LODs and how to write them, we can ask ourselves: what are LODs used for? LODs have many useful applications, these include the following:

  • Cohort analysis : comparing data for different sub-groups: this would answer business questions such as "Do longer tenured customers make a larger contribution to sales?"
  • Proportional brushing: this answers "Compared to what?" questions. e.g. "How much does each region contribute to total sales by product?"
  • Aggregates of aggregates: e.g. "What is the average of the sum of customer sales across several countries?"
  • Binning by aggregate: this answers questions of the type "How many had how many?" e.g. "How many products had how many sales?"

If you want more...

To see an example of Fixed LODs in action, check out my blog Fixed Level of Detail Expressions: Europe & Religions. If you want to learn about LOD expressions in more depth, you can  also check out Tableau's white paper on the topic. For more practical examples of how to make use of LODs, you can find a truly interesting list of use cases that Tableau has published in its "Top 15 LOD Expressions" article.

 

Nicolas Voirin

About the author

Nicolas Voirin - Graduate Analytics Consultant. I have started my career in the field of analytics because I have an interest in how data can be used to bring genuine value to organisations. With a background in Engineering and Management, I take pleasure in solving business problems and believe visualising data is a powerful tool to do this. I am curious about a range of topics but have previously enjoyed building data visualisations concerning politics and history.

Subscribe to our newsletter

Get in touch
Get the edge

Go to webform

Subscribe to our newsletter

Get in touch
Get the edge

Go to webform