3 different ways you can use LODs

LOD calculations in Tableau are like magic spells for your data. Just like Harry Potter needed to know different spells for different situations, you need to know which calculation to use depending on the analysis you’re doing. This blog post shows some examples of LOD use cases.

1.Find Min/Max in a Time Series

For example, we would like to know the minimum or maximum value in a time series. How would we highlight those values in the following chart? Firstly, we have to create a new calculated field with the following LODs. Here we will calculate the maximum and minimum total sales of all sales by month.

After that, we have to create an if formula so that the min and max are showcased together.

// Maximum sales
{FIXED : MAX({FIXED DATETRUNC(‘month’, [Order Date]) : SUM([Sales])})}

// Minimum sales
{FIXED : MIN({FIXED DATETRUNC(‘month’, [Order Date]) : SUM([Sales])})}

// Min or Max

IF SUM([Sales]) = MIN([Min]) OR SUM([Sales]) = MAX([Max])
THEN SUM([Sales]) END

After adding [Min or Max] to the rows, all we have to do is apply a dual axis and format the graph to our liking. This is how to use LODs to find min/max values on time series graphs.

2. Synchronize Chart Axes

When we have multiple vizzes on a dashboard or have dual axis vizzes, oftentimes it is hard to compare information as the axis of the charts is different. To avoid that, we could add a fixed axis, however, as the data changes, it would not solve the issue long term. As we can see here, it is hard to compare to regions as the axis are not synchronised on our dashboard:

To fix this, we have to create a reference line that will use the LOD value. We will get the sum of sales, by month for each region as follows and take the maximum value of that. We will do it in two different steps in order to avoid nested LOD.

//Max Sales by region

{FIXED [Region], DATETRUNC(‘month’, [Order Date]): SUM([Sales])}

//Max Sales

{FIXED : MAX([Month sales by region])}

Now we have synchronised vizzes in out dashboard. You can edit the format of the reference line to make it transparent and remove the tooltip and value.

3. Count Items Selected in a Filter

You can use LODs to show how many items you have selected in your filter. Let’s say we want to compare some specific states in sales using a bar chart and we would like to know how many states we have selected.

Add the following LOD to count the number of states:

// Number of states

{Fixed : COUNTD([State])}

Please note that this will not work unless you add the state filter to the context. Due to the order of operations, LOD will come first and will calculate the number of states overall in the dataset. Once you added the filter to the context, it will become grey. All that is left to do is to drop LOD onto details and add our dynamic name. Here is the result:

You can use LODs for many more purposes that are not covered in this blog post, like:

• Removing duplicate values caused by table joins
• Aggregate information outside of the vis level of details
• Create constants that are dynamic
• Isolate or aggregate values outside of the filters that are applied.

LODs are a powerful tool for working with complex data in Tableau and can help you to gain insights that might not be possible with standard aggregation methods.