Oh, LOD, why?! Reasons to Channel the Force for Level of Detail Calculations

A long time ago, in a version of Tableau far, far away, there lived sets and no Level of Detail (LOD) expressions. We could join SQL for a market basket analysis, or the adventurous could use sets cascading into calcs and re-cascading into sets and calcs. Then, a Jedi came forth, offering a new way, a new light: Level of Detail calculations!

Bora Beran has explained the wonders of these. My contribution to this is different pictures, worse jokes, and maybe some use cases. LODs are powerful calcs that, as Bora Beran shows us, can be used to bypass the conventional limits of our viz. It’s like data within data or, for my Excel junkies, the GETPIVOTDATA command.

We have 3 options:

1. FIXED
2. INCLUDE
3. EXCLUDE.

FIXED thus far has been one of my personal favorites. It locks everything in right at the level you choose and allows you to use it as a dimension. This, by far, sets FIXED apart from all others. But, why?

For starters, I can do a cohorts analysis on SuperStore data. I can look at my customers and aggregate on the first date of purchase. I do this first by invoking the Force and readying my light saber.

In English, for each customer, I’m finding the minimum start date within the data set. As Bora showed us, this will ignore both dimensional and measure filters, but not context ones.

Great, now what?

Let’s find cohorts in SuperStore!

So, I made a simple, default color bar chart and counted my unique customers. Next, I wanted to see when those customers first started buying by dragging my LOD (may the Force be with you) on the color. Looks like 2011 was a good year and, since then, it’s not been so hot for gaining new customers.

So, now that I know old customers > new customers, it’s time to figure out how people are buying so I can start a more effective marketing campaign.

Are there patterns in purchases?

For this, I’m choosing INCLUDE, so I can set the level of detail (Customers) and let the viz also help slice and dice the data.

{INCLUDE [Customer Name] : COUNTD([Product] )}

I’ll break out the Crayola crayons again and see if people have bought more or less over time.

So, 2011 bought less products, but grew a bit more each year.

Let’s see what’s in the (market) basket.

Now, if I want a straightforward marketing basket, I can join the source to itself and be on my merry way. If I’ve been doing that, I can tell it’s not making for effective marketing, so I want to switch this up a bit.

Maybe the trick is understanding the first purchase? We need more new customers and I need to knock this out of the park. There’s no half-homerun; it’s out of the park or I risk it landing in some catcher’s hands. I can work with my data scientist to create some complex model, or I can use the Force and claim glory.

Now, a customer can make multiple purchases at once, so what can I define as “first”? Maybe, for my purposes, it’s the one that’s most profitable, as this also highlights the purchases we want. Now, I already have the date of first purchase, so I can use that. Then I just need to sort out Max Profit by Product by Customer.

I start by finding the profit ratio by customer and product.

{FIXED [Customer Name],[Sub-Category] : SUM([Profit] )}

I then find the Max Profit ratio for my first purchase.

{FIXED [Customer Name],[Product Name] : MAX(
IF [Order Date]=[Min Order Date (Customer)] then [The Field Above] END)}

While I can create it as one field by cascading 2 LOD Expressions, I’ll need both for the next part. The INCLUDE on the inside picks up the dimensions on the outside:

{FIXED [Customer Name],[Product Name] : MAX(
IF [Order Date]=[Min Order Date (Customer)] then {INCLUDE :[Profit] } END)}

This gives me the Maximum Profit item for that customer. Next is segmenting out the Products by Sub-Category.

To identify the most profitable product:

{FIXED [Customer Name] :MAX(If [Profit by Customer – Most Profitable]=[Profit by Customer] then [Sub-Category] END)}

To identify all the other products:

{FIXED [Customer Name] :MAX(If [Profit by Customer – Most Profitable]<>[Profit by Customer] then [Sub-Category] END)}

Then build the view:

A few closing tips:

1. Brackets invoke the Force and make the whole thing work
2. Colons split the line between dimension and measures.
3. Logic can go inside the aggregation, but cannot go by the Force, as it bends minds.