How To Think Tableau Level of Detail Expressions as Writing SQL Group By

Three examples to demonstrate how I approach different business problems

Photo by Coffee Geek on Unsplash

Level of Detail Expression is an important concept for Tableau calculation field.

FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view. FIXED Level of Detail Expressions

When learning how to write LOD, I struggled a lot. I don’t know which column I need to put after fixed clause. One day, I came up with an idea. LOD is just like SQL’s group by function.

I decided to test it out, and I would like to share how I think LOD in SQL way, and it does help me to write LOD calculation in Tableau quicker.

For this demo, I would like to use the Tableau superstore dataset, and I have imported the file into my local SQL database.

1. Region Sales

When writing the SQL query, I used “region” as group by column. As for tableau, the region column is used as a fixed clause.

SQL

SELECT "Region", SUM("Sales")
FROM store
GROUP BY 1
ORDER BY SUM("Sales") DESC

Tableau

{ FIXED [Region] : SUM([Sales])}

For the tableau table, the Region Sales column used LOD, and the Sales use the table view

2. Customer Earliest Order Date Cohort

For this problem, please take a loot at sub-query. “Customer ID” are grouped by each customer id ‘s earliest order year. As for tableau, I used customer id as fixed clause.

SQL

SELECT year, COUNT(DISTINCT customer) as num_customer
FROM
(SELECT "Customer ID" as customer, MIN(RIGHT("Order Date",2)) AS year
FROM store GROUP BY "Customer ID")tmp
GROUP BY 1

Tableau

{ FIXED [Customer ID]: MIN(([Order Date]))}

In 2017, there were 595 customers who placed their first order. The number of new customers decreases dramatically year over year.

3. Region and category comparison table

SQL

WITH cte AS(SELECT "Region","Category", ROUND(AVG("Sales")) AS category_average
FROM store
GROUP BY 1,2
ORDER BY "Region", "Category"),
cte2 AS(SELECT "Region", ROUND(AVG("Sales")) AS Region_average
FROM store
GROUP BY 1)
SELECT cte."Region", cte."Category", category_average, Region_average
FROM cte
JOIN cte2 USING("Region")

Tableau

{ FIXED [Region1]: AVG([Sales])}

As for category average sales, I used table view to filter the result.

Main takeaway

Think of SQL, when you want to put a column as a group by column, you put the column as the Tableau fixed clause.

It is my way to think of Level of Detail Expression, and I found it is beneficial for me when I am stuck. I hope you will find this helpful:)