Application of LOD Expression

This is continuation of Part-1 & Part-2. If you haven’t read them, read them first. Then you understand clearly how to apply LOD calc.

LOD Expression in Tableau

Part-2 LOD Expression

1. Customer Order Frequency

If you want to find how many customers made 1,2,3,…N orders then we need to use LOD calc? This is a simple question but without LOD calc it will be impossible because as you see here we are trying to break the measure & comparing it with the measure.

First, write a LOD calc using FIXED expression.

From the above we can see for every customer_id, FIXED expression will count the distinct orders made by each customer. Build the Viz

1. Bring Count Distinct customer_id to Rows shelf.
2. Change the type from Measures to Dimension of customer order frequency calc.
3. Drag Customer Order Frequency to Column shelf. You will get this Viz.
4. Viz will look like this.

2. Percent of Total:-

I want to calculate the % of sales contribution by each state. This problem can be handled by Table calculation also. But I want to show you we can achieve the same solution by LOD calc.

Calculated Field:-

Build the Viz:-

1. Bring country map to the view
2. Bring “Percent of total” calc to Color & Text Marks card.
3. Use the formatting tool to change the lookup of the number.
4. Viz will look like this.

3. Using LOD to manipulate Dates in Tableau

When we build dashboards in Tableau, we always come across how to calculate the Last 15 days of Sales, or Last 30 days, or the Last 6 months of Sales. These Last N days/months come from the client-side. Some clients want to see how their businesses are doing. How many Sales are from the Last N days/month? Here I will go through the Last N days/month. You need to learn 1 concept and the other ones will be piece of cake.

# So the goal is to find the Last 15 days Sales

Build the Viz

1. Find the MAXIMUM date from order_date.

Write FIXED LOD calc because we want the maximum date based on the whole table:- MAX DATE

{FIXED: MAX(Order_date)}

2. Write another LOD calc:-

3. Bring “Last 15 days Sales” to the row shelf

4. Bring Sales measure to column shelf.

5. Viz will look like this.

# I will Show you how the calculation is working. You write

[Order Date] <= [Max Date]. This gives all dates below the maximum date.

# Then you write the

[Order Date] > DATEADD(‘day’, -15, [Max Date])

So what DATEADD function is doing that, it subtracts 15 days from the MAX date and finds the 16th December 2020 date, which is exactly 15 days behind from 30th December(including 30th December) then the condition is applied to show all the dates who is greater than 15th December.

The order_date behind the scene looks like this. See only the TRUE value which is showing only the Last 15 days order date.

See the table below.

Ok, Now we know how the Last 15 days calc works. We use the same concept to find

• Last 30 days Sales
• Last 60 days Sales
• Last 6 months Sales and etc.

a). Last 30 days Calc

b). Last 60 days Calc

c). Last 6 months Calc

4. Adding maximum & minimum value in Time Series

Sometimes we struggle to find the minimum & maximum value in Time-series. Example:- Let’s say you have a Time-Series like this see the below chart & you want to find the min & max values and you want to show them on the plot. In this type of situation, we can leverage the LOD expression.

Calculated Field

1. Min Value

2. Max Value

3. Max/Min Sales

Create the Viz

1. Bring Max/Min Sales Calc to roe shelf and turn on the dual axis & also synchronize the axis, after that change the marks from automatic to circle.
2. Viz will look like this.

From the above chart, we can see “red” dots showing maximum & minimum values in the Time-Series.

Note:- We can achieve this task using table calculation too in Tableau, but I wanted to show you the solution with LOD expression. Check the calculated field below.

Bring this calculated field to the row shelf & follow the necessary steps and you will end up with the same view.

# I will discuss more LOD expression & Table calculation applications in future blogs. If you like the blog please give a clap