I’ve been following with great interest the discussion in the Tableau community surrounding various approaches to using Level of Detail Calculations (LOD calcs) to solve various problems. On the one hand LOD Calcs are often very easy and intuitive. For example, it easy to determine an aggregation at any level of detail:
- How many unique customers per product?
{FIXED [Product] : COUNTD([Customer ID])}
- Maximum sales of a Category per State?
With State in the Viz Level of Detail: MAX({INCLUDE [Category] : SUM(Sales)})
But what if I want to know what the maximum category actually was? That’s a little harder. Mark Jackson recently posted on how to find a dimension at a lower level of detail along with a follow-up post based on an approach suggested by Joe Mako. Jonathan Drummey had a similarly outstanding post which demonstrates his thought process in tackling such a problem.
What I’d like to do here, is replicate my thought process in solving a similar problem. M thinking has been very much shaped by interactions with Mark, Joe, and Jonathan and my approach is very similar – but I find it very useful to see the slight variations in the different ways individuals approach similar problems. And I’m going to extend the results a little to explore what kinds of analysis are possible after solving the initial problem. I very much welcome any feedback!
The initial problem
Let’s say I want to know the Category with the most sales. We’ll start by assuming I want to know the highest selling Category in a Department. But later, I might want to swap Department for any other dimension (such as State or Region).
(follow along with this workbook)
I’ll start with Department and Category in the view, although ultimately, I don’t want Category in the view. But having it there initially will help me see how things work at that level of detail:
First, I need a Level of Detail calculation that gives me the maximum Sales for all Categories in a given Department:
The hardest part of thinking through nested LODs is that I started with a view that is at a different level of detail than what I ultimately want. Here, I already have Category as part of the viewLOD (view Level of Detail). I’ve done that so I can see the results unfold before me, but I have to keep in mind that I’m going to remove it later, so I start with an INCLUDE to get the Sum of Sales for each Category.
Then I want to back up to the Department level to determine the MAX of all the Sums of Sales for each Category within a given Department. So I EXCLUDE Category and find the MAX.
Now, I have the Sales of the highest selling Category within each Department and it’s easy to see which Category has the highest sales for the Department (it’s the one where the SUM(Sales) matches the MAX Sales):
So, I’ll write one more calculation to determine which Category has a Sum of Sales that matches the Max. Here it is:
The trick here is to use INCLUDE to make sure I’m doing my comparison at the Category level (just like I did visually in the view above). Since I want this to work even when I remove Category from the view, I’ll INCLUDE it here.
And now, I can remove Category from the view and only see the Max Category for each Department:
Sales here are the total sales for each Department, but I also have the Category with the most sales and the actual sales for that Category.
Extending the example
The best part is, I can add other dimensions and even swap other dimensions with Department. For example, here is the highest selling Category for each State:
And I can even re-arrange the view to get other types of visualizations, such as this map:
And I could use this same approach to answer other questions (such as, which category has the lowest profit in the east?)
But let’s take it one more step…
What if I wanted to know how many states for each Max Cateogry? In how many states were Office Machines the highest selling Category? I could count them visually in the map or in view like this:
But, even better I can let Tableau do the counting and then visualize the results!
If I allow the Max Category to partition table calculations (simply right click the field on Rows and uncheck “Ignore in Table Calculations”).
This is the best part of the LOD calcs – they are aggregates (or sometimes even row-level) results that can be used to partition table calculations. That gives us another layer to solve very difficult problems without having to layer complex table calc on complex table calc.
Then, the Size() function (computed along State – right click the field in the view and select Computer Using > State) will tell me how many states per Max Category:
State has to be in the vizLOD for the Size() function to return the right results. But I don’t need to see the same result repeated state after state, so I will use the First() function (also computed along State) to keep only the first result of Size().
Moving, First() to Filters, State to Detail (it has to be in the view, but I don’t need to see it) and Size() to Columns allows me to have a view like this:
Wow! Tables were the highest selling category in 11 states! I should double check profit…
Everything up to the Extended Example could have been done fairly easily with table calcs. However, using the LOD calcs allowed us to further partition table calcs that allows our analysis to go even deeper without having to get into incredibly complex table cacls.
I knew I loved LOD calcs when I first saw them. But the more I work with them, the more I see the possibilities…
Thanks for the walkthru but I got an error when opening the workbook to follow along- its trying to connect to SQL server. Can you repackage and post?
Thank you very much for pointing that out and sorry, it wasn’t extracted. The workbook should be good to go now. Please let me know if you see any other issues.
now it works- Thanks!
Great article. Really enjoy how you’re answering real-world questions that getting Management’s juices flowing 🙂
Thanks Gordon! I’m happily using Tableau to solve real world problems every day, so it only seems natural. Of course, it’s also fun to use it to paint pictures, plot Star Trek deaths, and play Tic Tac Toe! 🙂
This is great… I have been struggling to get LOD calcs, but I somehow intuit that they are the answer or part of the answer to my biggest headache. Combining the previous post on dates and this one on LOD, can you suggest how I might produce an “aggregation exception” like the one in BI for dates – what I’d like to do is tell Tableau that my headcount measure should never be aggregated beyond the month even when I do aggregate other measures at the year or quarter level? Headcount is redundant month to month because many of the same employees are in it each month (so you can’t add two months), but other measures like hires and terminations can be aggregrated by month, year, etc. Can I fix my headcount measure to the month level so it always just gives me the last month of the period? The following calculated measure works, but only when I set the table calculation to calculate at the Pane or Cell level. I’d love to have it just work. Any advice would be welcomed. Thanks.
HC by Month:
IF INDEX()=SIZE() then AVG({FIXED [Month (MY)]: (sum(if [Metric Type]=”Headcount” THEN [Number of Records] ELSE 0 END))}) END
where Month (MY) is a custom date part of Year Month.
Eric,
Sure. It would likely end up being a nested calculation with code like this:
{FIXED : SUM(IF [Date (MY)] = {FIXED : MAX([Date (MY)])} AND [Metric Type]=”Headcount” THEN [Number of Records] END)}
Here’s the breakdown:
1. Get the maximum date in the data set
{FIXED : MAX([Month MY]) }
2. That returns a record level result, so we compare with the date for each record level (and include the metric logic you had):
IF [Date (MY)] = {FIXED : MAX([Date (MY)])} AND [Metric Type]=”Headcount” THEN [Number of Records] END
3. Place all of that inside another FIXED statement to make it available for all records (not just the final month records):
{FIXED : SUM(IF [Date (MY)] = {FIXED : MAX([Date (MY)])} AND [Metric Type]=”Headcount” THEN [Number of Records] END)}
That should give you a calculated field for headcount that is returning the headcount of the final month. Hope that helps!
-Joshua
Thanks Joshua. That does help. It at least prevents inappropriate aggregation of headcount across months, quarters, years, etc. But this only sets a single value for all months. So if I display more than a quarter or single year, I get the same last value for every period. I posted some screen shots here to help show what I mean. Note that your calculation fixed the issue that happened when not showing the month, but now I have the opposite issue of the month actual value not displaying when other previous months are displayed. Ultimately, I need a calc that somehow dynamically determines the max month for each period displayed whether it’s month, quarter, etc., and show that value. The screen shot called “monthly” shows what I mean.
Thanks again for your help so far.
Eric
Eric,
In this case, it looks like a table calculation would likely be much easier. So, you could have something like:
IF LAST() == 0 THEN SUM(HeadCount) END
and compute that along month. It will require the month to be in the view level of detail. You can move Month to the Detail of the Marks card if you don’t want to show it — or, if you want to show Month and Quarter together, you can use the above calc but then next it into another calcualtion, like WINDOW_MIN:
WINDOW_MIN([Last Month Value])
[Last Month Value] would need to be computed along the Month and the Window_Min would need be addressed using both Quarter and Month.
Hope that helps!
Excellent stuff…. Keep sharing more n more business scenarios.
Simply awsome
I understand this post was from some time ago but I’m working a project where I think LOD could help.
I’m trying to track occupation code changes (and other status changes) by employee over time. As a brief example, if an employee ID had an occupation code of “A” on Jan. 31, 2016, but entered occupation code “B” Mar. 31, 2016, how would I track that change? The ‘”A” Team’ is interested in how many employees are transferring to other occupation codes.
Are LOD expressions appropriate for this scenario? What would this LOD statement look like?
I’m new to LOD and a solution for tracking employment changes by employee, organization, etc. over time would be a HUGE win in analyzing workforce data.
Thanks!
Hi Joshua,
I have build a view for displaying the quarterly Headcount of the organization and it would always be the last month of the quarter. I used the tips above and was able to show the numbers. But my Problem starts when the data is for the month of November and we have 2 (Nov & Dec) more months to go for Q4. Now whatever I do the Q42015 doesn’t show me the number with the below formula and it always adds up or show number of a month that I hardcode. Could you please help and suggest the correct formula that I could use. This is so critical for me at the moment.
What it is doing at the moment is the for 2015 it is displaying the number for Oct as Q42015 (since I am passing 10) and for 2016 it is anyway fine as Oct number is the one we should show.If I don’t do this then Q42016 doesn’t work.
Your help is really appreciated on this please.
IF MONTH([Reporting Month])=3 AND year([Reporting Month]) <= YEAR({fixed : max([Reporting Month])}) THEN 1
ELSEIF
MONTH([Reporting Month])=6 AND year([Reporting Month]) <= YEAR({fixed : max([Reporting Month])}) THEN 1
ELSEIF
MONTH([Reporting Month])=9 AND year([Reporting Month]) <= YEAR({fixed : max([Reporting Month])}) THEN 1
ELSEIF
MONTH([Reporting Month])=10 AND year([Reporting Month]) <= YEAR({fixed : max([Reporting Month])}) THEN 1
Else 0
END
Correction… data is till October and not November. Reporting Month is basically Date of Report which is always first date of the month
What about a Window_Sum?
I have a LOD I am trying to create. For an Inventory Turns Calculation I need to look at a Cost of Goods sold Dollar Amount for the past 12 months.
In the view the user is filtering from 9/2016 thru 9/2017. On 9/2016 turns calculation looks odd as its not looking back at the previous 12 months given the filter. It should go back since the beginning of the data 2014. It is my understanding that this would be a good use case for LOD. In my attempt to make this work I get an error though “level of detail cannot contain table calculations or the attr function”.
My LOD is Fixed and is trying to look at {FIXED[Region] : WINDOW_SUM(Sum([COGS]) , -11, 0) }
My guess is that you want:
WINDOW_SUM(SUM({FIXED[Region] : Sum([COGS]) })), -11, 0)
Which moves the table calculation outside the LOD expression. LOD expressions allow for simple aggregations in the expression, but the table calculation would apply after the LOD is evaluated. Hope that helps!
Hi,
I am currently working on a dashboard that uses LOD calculations to show the amount of media spend allocated to a set of different theme parks year-to-date (I’m using these as dynamic inputs for automated insights).
I am kind of stuck at the moment, I want to add week-over-week % changes in spend per park but haven’t been able to find the LOD formula that I can use to plug into the text mark.
Could you help out possibly? My LOD spend calc is basic but works :
{ INCLUDE [Park] : SUM([Capped Cost]) }
Thank you!