LODs of Fun with Jedi Filters

Tableau is a playground for creativity. When a bit of knowledge meets imagination, a Tableau Jedi is born. Understanding a couple of key facts about Level of Detail Expressions can save you hours of trying to guess Google search terms for answers to tough questions.

You can use Level of Detail Expressions to enable powerful filtering scenarios, all based on two facts:

1. Fixed expressions can be dimensions.
2. Fixed expressions are evaluated after context filters and before dimension filters.

Our whitepaper on Level of Detail Expressions provides a more general overview, and our blog post on the top 15 LOD Expressions provides more introductory examples. This post will focus on the top five Jedi examples. You can download the workbooks for detailed solutions.

The devil is in the detail. Aggregating data to a higher level can cause exceptions at a lower level to disappear.

For example, suppose a company would like to identify where it is giving discounts above a certain threshold. The following view shows that agriculture is the only industry exceeding the discount threshold. In absolute terms, this represents such a small number of units that it can be easily disregarded. However, it would be erroneous to conclude that there are no problems based on the summary view, as it is possible that individual customers within an industry are receiving a high discount rate.

The orange dots surface this detail, with the number of dots indicating the number of customers receiving discounts above the threshold. You can immediately see that there are three issues in the transportation industry, and clicking on a bar will filter to the customer detail view.

A Level of Detail Expression is required because the discount rate must be aggregated by customer, which is not in the visualization. More specifically, a fixed expression outputs a dimension that splits the orange dots into multiple points. If you are not convinced, change the expression to use include, and see what happens!

Which products are often purchased together on the same order?

Selecting a subcategory header will display the number of units purchased with the selection versus the total number of units sold for each subcategory. The percent of total is the ratio of these values. Selecting multiple subcategory headers will display the number of items sold on orders that purchased ALL of the selected subcategories.

The ingredients for this visualization are:

1. A dual-axis chart between the number of items fixed to subcategory and the number of items.
2. Order ID on the second marks card.

Order ID must be in the view so that it can be the target dimension in the action filter, which keeps all products purchased on orders that bought the selection. The brushing effect happens because the action filter is applied to number of items but not to the fixed expression. Hopefully now you can see why it's incredibly useful that fixed expressions ignore filters!

What is our year-over-year growth rate versus our competitors’ growth rates? How do the rates compare by product?

Selecting a competitor in the scatter plot will filter the bars to display the difference in our company’s growth rate from the competitor’s growth rate for each product.

The challenge with this example is that using an action filter on a competitor filters our company out of the bar chart. But we need to use our company’s data when calculating the difference in growth rate.

Because fixed expressions are evaluated before dimension filters, a Level of Detail Expression makes it possible to include data that has been excluded from the view in a calculation.

Which customers purchased consecutively in Q1, Q2, Q3, and Q4? The interesting insight in the view below is that the customers who purchase most frequently are not the customers who generate the most revenue!

By default, selecting multiple quarters will show all customers who purchased in at least one of the selected quarters. To generalise, an OR operator is applied between members in the same quick filter. The example described here requires an AND operator to be applied to the selected quarters.

We can use LOD Expressions to derive this filter criteria. One expression counts the total number of quarters that have been selected in the quick filter, and the other counts the number of quarters by customer. These are equal for customers who purchased in all selected quarters. Quarter must be a context filter. Otherwise, the expression would count the total number of quarters in the data set.

Have you ever wanted to select a region on a map and have it automatically drill down to the next level? Hover over any state in the map below and select “keep only” in the tooltip. Select “all” in the quick filter to reset the view. Then repeat, because it’s so much fun!

An LOD Expression counts the number of states that have been selected, similar to the previous example. A logic statement returns the state field if this is greater than 49, and returns the city otherwise.

A fixed expression is required since the output of the logic statement is city or state, which is a dimension. Equally, the filter must be a context filter so that the fixed expression counts the number of states in the viz rather than in the whole data set.

Many of the examples shown here use broadly-applicable techniques. Consider using LOD Expressions if:

1. The data needs to be aggregated at a different level than the visualization is displayed.
2. An aggregate calculation needs to be a dimension so that it can slice data.
3. A filter must apply to only some fields in the view.
4. The calculation must include data that has been filtered out of the visualization.
5. The calculation requires comparing the same column across different rows. (Basic expressions are only able to compare different columns across the same row.)
6. You are mixing aggregate and non-aggregate fields in the same calculation.

Please post your comments and questions below. We look forward to seeing further applications on the community forum!

Bethany presented these examples at the Tableau Conference. You can watch her session as part of the Virtual Tableau Conference.