Lorna was being very kind with Workout Wednesday Week 16. The chart was pretty simple to build and the LOD required would have been easy enough to write (though I would have used a table calc). However, Lorna wanted us to get familiar with the Quick LOD feature that came out in Tableau 2021.1. Quick LODs are a super fast way to create FIXED level of detail expressions.

---

In this week's tip, I show you how to do market share analysis with Level of Detail expressions. In this video, I show you the calculations, then I expand it to make it more dynamic using a parameter.

Enjoy!

This week's tip came about when working through a question at the Data School during our client project last week. The question was essentially:

How can I color each line on a line chart by the most recent value?

This requires a three-pass LOD expression. The key is to realize that Tableau created an aggregate table for each LOD working from the inside out. So

1. Tableau calculates the inside LOD first.
2. Then the second LOD is calculated based on the results of the first LOD.
3. Then the third LOD is calculated based on the results of the second LOD. 

Make sense? I hope so! If not, let me know. Enjoy!

This week's tip is a nice use case for nested level of detail expressions and also a nice test for my "LODs in an English sentence" method. It started with this tweet from Charlie Hutcheson:

Tiny #MakeoverMonday viz this week. One of my best mates goes on the Manly route as his daily commute. Lucky guy! https://t.co/WnxLuTYi1A pic.twitter.com/G3eBOCbg1F

— Charlie Hutcheson (@CharlieHTableau) May 1, 2017

If you scroll through the comments, you'll see that Charlie was hoping to do this via LOD, but couldn't quite get it. My tip this week solves this problem.

Enjoy!

UPDATE: In the first iteration of this video, I was explaining the FIXED LOD like I would explain an INCLUDE LOD. I have fixed that and re-recorded the video. Thanks to Graeme Wiggins and Bora Beran for the feedback!

---

Back in February, I wrote about putting table calculations into an english sentence. This concept can be easily extended to level of detail expressions. As such, I'm going to kick off a series of blog posts that show how to take ANY level of detail expression and turn it into an english sentence.

First up are three use cases for FIXED LODs.

How many customers placed how many orders by region and product category?

How many products sell in how many regions by year and quarter?

What is the total number of customers acquired by region by day?

Here's a video of how I created each of the scenarios and the examples for you to download and follow along. Enjoy!

I'm on a plane headed for an 8-day holiday with the family, so no video tip this week. I doubt my neighbors on the flight would appreciate me creating a video. I'm working on a series about LOD expressions to put them into an easy to understand sentence much like I did with table calculations (read that post here). For me, the easiest way to make sense of LODs is to go through lots and lots of examples.

This post will walk through a pretty simple, but very applicable use case. Let's say you've set goals at your company that you want to sell £500 worth of products to each customer each quarter. But you also need that goal to be flexible. That is, the user needs to be able to change that amount.

First, we need to create a simple view that has Year and Quarter on the Columns shelf. I'm going to use continuous quarters.


Next, we need to create parameter to allow the user to enter the minimum threshold. To do that, I'll right click on Sales and choose Create -> Parameter. This automatically creates the parameter type that I need. I'm chose the All option in the Allowable values section because I want my user to be able to type in any value. I also set the Display format to currency.


Right click on your parameter and show the parameter control. The parameter doesn't do anything until I use it in a calculation. What we want to do now is create a level of detail expression to count the number of customers that met the threshold in each quarter. First, though, we need to determine the sale for each customer in each quarter. In a plain English sentence this would be:

For each customer, year and quarter, calculate their total sales.

Since customer isn't in the view, that's our clue that we need to use a level of detail expression. The calculation looks like this:


Ok, now I need to determine how many customers met the threshold. To do this, I'm going to edit my calculation and add a simple IF statement.


This calc is looking at the sales for each customer in each quarter and if that customer meets the threshold, we count them. Otherwise, we don't count them. Dropping this into onto the Rows shelf, we now get a simple line chart.


As you change the parameter, every recalculates perfectly. Great! Now we know how many customers met the threshold each quarter. The problem now is that we don't have a lot of context. Is the 210 that met the £500 threshold in January good? Maybe we should have a secondary goal that X% of customers need to meet the target. In other words, 210 in January represents what percent of all customers?

To answer that we need to create another LOD expression to count the total customers.


One more calculation and we're nearly done. This time we want to calculate the % of customers that met the threshold.


Format this calculation to percentage (I like one decimal) and then replace the measure on the Rows shelf with this new calculation.

Great! Much more context now, but we can make it even better. Let's set an additional goal. How about we give our user the option to set the % of customers that need to meet the target as well? Let's start by creating a parameter to allow the user to enter the % of customers meeting the target.
Show the parameter control, add a reference line that uses the Target % value, change the Quarter field on the columns to discrete and change the mark type to bars. Make the bars as wide as they'll go and add a light grey border.

From here, I would create an additional calc that highlights the bars that are not meeting the threshold. Why? Because those are the areas of concern. The calculation looks like this:


Dropping that onto the color shelf and tweaking the colors, I now have a pretty complete view. The last thing I would do is adjust the format of my quarters and put it all together in a dashboard.

Hopefully you found this a good use case and explanation. It's also helped me realize how much easier it is to create videos.