In my previous blog, I went through some of the fundamental concepts associated with LOD Expressions. To be able to use LODs accurately and efficiently, it is key to understand both the granularity of the data set and what elements affect the level of detail on the view. If you are unsure about these concepts, you can refer to my previous blog here.
The goal of this blog is to provide some common use cases of LOD Expressions to showcase their capability to facilitate more in-depth analysis. I will focus on the Fixed LOD since it is the most powerful and useful type, but will also give an example of using the Include LOD. All examples use the same Sample Superstore data set that comes with Tableau and I’m using Tableau version 2020.3 but older versions should still give similar if not the same results. Let’s get started!
Cohort Analysis
One especially interesting application of Fixed LOD is what’s often referred to as cohort analysis. In business settings, one key metric is the sales volume and decision makers are often interested in knowing how much sales contribution is made by each customer cohort. Depending on when they first made their purchase with the company, customers are typically classified into different cohorts.
Image that Sample Superstore were a real business. We may want to classify the customers into different groups based on which year they made their first purchase and then look at the trend for each cohort in terms of the percentage contribution they are making to total sales.
To do this, we first need to give each of our customer a tag, such as the year of first purchase, which can be calculated by order date. This tag can then be used as a dimension and placed on the color marks card, just like the way we would use the region attribute. The calculation looks as follows:
Now in every row, each customer will have a tag. Since our purpose is to show the percentage of sales contributed by each cohort for each year of orders, the next step is to drag order date onto the column shelf and sales onto the row shelf. Note the order date should be in discrete year and we are summing sales. Then place the field we just calculated onto the color marks card and you should be able to see sales broken down into four distinct groups, i.e., one for each year. Finally, use the quick table calculation to find out the Percent of Total for sales instead of showing absolute values. Remember to change Compute Using to whatever is the field on your marks card. This is what the end result would look like:
With this kind of analysis, even though we only have four years of order-related data, it’s very easy to tell that those who have been with the company for the longest are still generating the most value for our business. This can be a very important insight as it is critical for our business to maintain good relationship with those people.
Order Frequency
Using the same business context as in the previous example, another useful analysis that can be done is finding out the order frequency of our customers. In other words, we wanna know how many people have made how many orders in a given time period. In this case, we need to show number of orders on the horizontal axis and number of customers on the vertical axis. By using a Fixed LOD Expression, we’ll be able to calculate the number of orders for each customer and add that as a tag to our original data set. In Tableau, make the following calculation:
This calculation can then be placed on the column shelf. And the number of customers can be easily obtained by performing a distinct count of customer name. The resulting view should look something like this:
As can be seen, I’ve also added an annotation so that the information displayed on the chart becomes more clear. Something even more powerful is reusing the first order year tag created in the first example on the color marks card. That will allow us to dig out some more interesting insights. Feel free to try it out!
Average Sales on a Map
In this last example, I want to demonstrate an application of the Include LOD and touch upon one type of calculation that can be especially confusing and error prone for beginner and even some intermediate users. And that is the calculation of average sales. The view we are trying to create here is fairly straightforward – a state map but with each state colored and labelled by the average sales across all cities in that particular state, like the following:
Clearly, this view involves two different levels of detail. For the map, the level of detail is state, whereas for the color and label the granularity is city. In other words, we are showing an additional dimension in our view but without adding it onto our view. Include will do the trick.
Obviously, we first need to create a state map, which can be done just by dragging and dropping the state field onto the canvas. Then for each city, we need to figure out the total sales. When this measure is added to the view, we need to average it because the cities will be grouped into different states and for each state we need to take the city average. The Include LOD used here is given below:
Some people may be wondering why can’t we just create a state map and use the average of sales? Well, this is the pitfall many inexperienced users may fall into. The reasoning is that since the granularity of our data set is per product name per order ID, the average you will get is an average across all those rows for each state. Thus, the value will be significantly lower.
Wrapping Up
I hope you have gotten a deeper understanding of LOD Expressions through these examples. Obviously, this only represents a small portion of the enormous capabilities of LOD Expressions. Thanks for reading and feel free to comment, share and reach out to me on LinkedIn. Also, if you would love to dive deeper, I strongly recommend checking out the following blog posts :
Bethany Lyons / Top 15 LOD Expressions
Ken Flerlage / 20 Uses for Level-of-Detail Calculations
Author: Romy Li
Data Consultant DSAU6