This week we're doing an in depth exploration of Tableau and some of its associated forms (Prep, Server, Desktop...) and one of the new things that we have learnt about is Level of Detail (LOD) calculations, specifically in Tableau Prep so far. Don't worry, there's a far simpler way to do LODs nowadays than the method detailed in the feature image so read on to find out more.
Types of LOD
Whilst in Tableau Desktop you can do three different types of LOD (FIXED, INCLUDE and EXCLUDE), you can only do a FIXED LOD in Tableau Prep as INCLUDE and EXCLUDE talk about items that you have in your view and you don't have that in Prep.
So what does a FIXED LOD mean? Well, it's all to do with granularity. And what is granularity, I hear you ask? Well, it defines the amount of information that you have in your data. I like to think of granularity like a bag of salt. If something has low granularity, the salt is coarser (think sea salt) and you have fewer grains whereas if something has higher granularity, the salt is finer (think table salt) and you have more grains. In this analogy, my grains of salt represent information so low granularity = coarse salt = fewer pieces of information. High granularity = table salt = more pieces of information. If this just works in my brain as an analogy, I'd recommend reading this excellent blog post about granularity from an earlier member of the Data School: https://www.thedataschool.co.uk/reshika-chilakapati/explaining-granularity/. But what does all of this have to do with FIXED LODs? Well, it's in the name - Level of Detail.
What is a FIXED LOD then?
What you're asking in a LOD is, if I specify (FIX) the granularity of my data, what are the resulting values I see in my measure? Below is an example of an FIXED LOD calculation:
{FIXED [Region]: SUM(Sales)}
In the LOD above you're asking at the region level, what is my sum of sales? If using the sample superstore dataset, this will return you four values - one for each of the regions. If you delved deeper into your data, you'd see that all of the states in each region would show that region's value for the sum of sales in the data pane. You would be able to get the distinct sum of sales at the state level as well (in a separate calculation), but you would gain this comparative value from your LOD. You can also add multiple dimensions to define the granularity of the data you wish to be returned. For example:
{FIXED [Region], [Category]: SUM(Sales)}
Here you're asking about the sum of sales at both the region and category level. If using the sample superstore dataset, this will return you twelve values - one for each category in each region. Using multiple dimensions wouldn't make sense if using ones in the same hierarchy (e.g. region and state) because you aren't adding anything to your level of detail by including the extra dimension. You can also use any aggregation that you choose, so long as it makes sense!
{FIXED [Region], [Category]: COUNTD(Customer Name)}
Here you're asking, what is my distinct count of customers (as customers might make multiple purchases and therefore appear more than once in the dataset) at the region and category level? This will again return twelve values but different ones to those in the earlier sales calculation.
How do I implement them in Tableau Prep?
Before answering that, you might be wondering why you would implement them in Tableau Prep. This is more important if many others will be using your data and consistently using this LOD calculation. Implementing the calculations in the data preparation step could save lots of people time later on and prevent potential errors occurring. Otherwise, it isn't absolutely necessary to implement them in Tableau Prep (that's why you can do them in Tableau Desktop) but it might be helpful to yourself later down the line. To write a FIXED LOD calculation in Tableau Prep is also a lot easier than in Tableau Desktop as it takes you through the steps very logically. Some food for thought either way, but without further ado, the actual point of this blog post: how to write a FIXED LOD calculation in Tableau Prep.
Step 1: Go to your data pane and find the measure you want to investigate. Click on the three dots in the corner > Create Calculated Field > FIXED LOD. (N.B. In this example, I went from my dimension instead and it doesn't matter too much which way you go from).
Step 2: A pane will pop-up with everything you need to complete. Rename your calculation. Add in the dimension that you're fixing the measure at. Add the aggregation of your measure. Click done.
The basic FIXED LOD is complete. You can do extra things now, as with my earlier examples if that's what you're looking to do.
Multiple dimensions in your LOD: Do the same steps as above but go to the plus next to the group by function and add your extra dimension.
Different measures in your LOD: Do the same steps as above but check your aggregation and measure.
So that's FIXED LODs in Tableau Prep. Fairly simple I think once you get your head around the steps to do them. My final top tip with FIXED LODs and this actually follows for all quick table calculations that you can do in Tableau Prep, is that you can view the calculation which is used to create your new column in the changes section. This is helpful for understanding the logic behind your calculation.
That's everything from me for this week. For now, onwards to week 6!