Hey, it's Tim here, and in today's video, we're taking on one of the most requested videos on this channel, and that is the topic of Level of Detail calculations. Today, we're starting with the Fixed Level of Detail calculation, and before we get stuck into the video, just a quick reminder that if you like the videos that I share on my channel, please do share them with other people, like, subscribe, and hit the notification bell so you can get the notifications when I upload new videos, okay? Without further ado, let's get stuck in. Okay, so over the last couple of days, I've actually uploaded two videos on my channel that cover some concepts that we're going to use today. So, if you haven't had a chance to check those out, these are the two videos that I'm referring to. The first one is an introduction to Order of Operations. This is important cuz we're actually going to be using that technique to understand which Level of Detail calculation we should be using. The other video is about understanding the granularity in your data set. Essentially, understanding what does each row represent in your data set. That's also another key concept to understand really, really clearly before you start working with LODs. So, if you haven't had a chance to check out those videos, or even just understand those topics in general, go out to YouTube, go out to Google, and understand those topics in detail cuz you'll need those concepts in this video, okay? Let's head back to Tableau, and let's open up a very simple visualization. We're actually going to build one ourselves. We're just going to go into Superstore here, and we're going to select the American version, which is the second one. If you don't see that one, just connect to whichever version that you have available, and you should get something like this. Now, when we work with visualizations in Tableau, there's this concept called the viz Level of Detail, and the viz Level of Detail is essentially the level of detail that we have in our visualization. Now, there's something I'm really conscious of in this video, which is how many times can I say Level of Detail before actually explaining Level of Detail, and so, I want to do that right now, okay? So, in our visualization, let's just build a very simple chart. Let's bring Sales onto Rows, and then let's bring uh let's bring a Sub-Category onto Columns, okay? So, we've got a very simple visualization here, and in essence, the viz Level of Detail refers to certain dimensions that we have in certain places in our visualization. Let me try and highlight that for you. If I just grab my highlighter here, and I just highlight the squares that represent sort of the viz Level of Detail that you should be aware of, the first is, of course, the Columns and Rows. Any dimension that you put into these two groups will fundamentally change the level of detail that we have in our visualization. What do I mean by that? Well, let me show you. If I then drag Category onto Columns, you'll see that my visualization changes to add the Category here into the Level of Detail that I'm seeing in my visualization. There is, however, more detail and granularity in this data set. It actually goes down to the Product level, but if I put anything here on the Columns and Rows, you'll see that it changes my visualization, okay? Now, you can change your Level of Detail very easily just by putting dimensions in other places. So, for example, if I was to drag Sub-Category here and put it onto Color, you'll see that that affects my visualization, and if I was to then remove it from the Rows, you'll see that it still remains in my chart, but this time, as represented by color. So, the Sub-Category, although it's not in the chart, is still controlling the viz Level of Detail that I've got here, essentially working at the Category and the Sub-Category level. If I was then to hit plus again, you'll see that this now goes to each and every manufacturer, so you can see that this is actually split out. If I change the colors here a little bit, you'll see that actually, we can see a lot more manufacturers, and so, although my chart hasn't changed—it's still the same bar chart with still the same totals—the Level of Detail is changing each and every time I add a new dimension onto the Color or the Detail pane. You'll see that these two are actually on the Detail pane, as shown essentially by this mark. You can just see that this icon here is exactly the same as these two, okay? So, just something to be aware of: anytime you add something to the Marks pane and the Columns and Rows, it changes. The only exception, in fact, is actually this one here: the Tooltip. So, if I was to just essentially put something onto Tooltip, let's say I put Segment onto Tooltip, you'll see this doesn't change my Level of Detail in my visualization. So, let's just drag these two away, so you see we've just got Sub-Category. And then here, we've got Segment here in the in in the um Tooltip, and you'll see that it hasn't changed anything. And if I hover over, you'll actually see that it uses a star, and this is actually in relation to something else called the ATTRIBUTE function. Of course, you've guessed it: I've already made a video about this on my channel, so go check that out if you want to know more about why it uses a star and not actually list out the segments, okay? So, we now understand what changes the viz Level of Detail. If I just go here to a line, there is another one that can sometimes happen. So, it's essentially um these uh these ones in the top row here: the Label, the Detail, and essentially the Path, and then anything obviously we've got in our visualization, and then lastly, the Columns and Rows, okay? So, these are the places that can change the Level of Detail in our visualization. Everywhere else doesn't change the viz Level of Detail, it's essentially happening elsewhere. It doesn't change what we see visually. So, that's the first thing to understand: what is the visualization's Level of Detail, okay? Now, this is important because when we start asking questions of our data, we need to be able to understand what's actually going on. So, let me go to a new sheet and just uh pose a sort of a new challenge to you. Uh this is actually going to be a good use case for the fixed Level of Detail that we'll come to very soon. So, let's just drag Sub-Category onto Rows. I'm going to drag Category and put it in front of it, and then I'm going to drag Sales, and this time, I'm just going to put it on this table here, okay? The last thing I'm going to do is go to Worksheet and show the Summary window, and then I'm just going to drag the Summary window here to the left-hand side underneath my Filters pane, so it's nice and easy to see. Now, the interesting thing here is if we wanted to calculate the percentage of total within each category for each sub-category, then I'd essentially need to understand the context of what the total was. So, if I go here and just select Category, you'll see that the total here is 742,000. If I just expand this over here, you see the Summary window is like a calculator. If we click on something, it does a bunch of aggregations for us and just gives us the value. If I go to Office Supplies, it's 719,000. If I go to Technology, it's 836,000, okay? And so, what I can very easily do in Tableau is just go in here, create a Quick Table Calculation, and select Percent of Total. Now, what will actually happen is it will do a percentage of total across the whole data set essentially, okay? And in this case, we don't actually want that, we just want one for each category. So, in this one, I'm going to cheat a little bit, and I'm going to say "Compute using the pane", and I'm just going to leave it at that, okay? Now, the pane in Tableau is essentially this particular window here. If I just highlight that there, you can see it very, very clearly. And so, what's important here is that this percentage is essentially going to add up to 100% when I'm looking at the category. So, let's just do that. If I click on Category, you'll see here that the sum is 100% for the percentage of total sum of sales, and for the whole table, you see it's 300 because essentially, there's three panes here in the visualization, which adds up to 300%, okay? Now, the frustrating thing is, let's say I want to keep these percentages of totals whilst only looking at certain sub-categories. Let's say I always want to know the percentage of total for the category within the sub-category um when I only look at chairs. Now, if I was to exclude everything and just keep just Chairs, you'll see this percentage changes to 100%. And fundamentally, this is caused by two problems. The first one is to do with order of operation. Now, the order of operations dictates that essentially, filters and certain calculations happen in a specific order. Check out my other video on this topic to find out more. But essentially, what we're doing when we add that Chair filter is we're essentially doing this: we're running a Dimension filter. And the problem we have is that our totals and all our aggregations are actually calculated much further down here. So, if you just go in here, we're looking at Quick Table Calculations, actually operating at this level of information down here. And so, the difficult challenge is: how do we keep the context of the question we're asking whilst also using a filter, which is actually happening before the calculation that I do? Well, this is where Level of Detail calculations are really handy because they they actually have a different position in the terms of the order of operations in terms of when they run. So, if I just change to red here, and I just highlight a few things, you can see that the fixed Level of Detail actually runs before any dimensional filters that we might have in our data set. You can see right here, it just runs in between Context Filters and Dimension Filters, okay? So, we're actually going to take advantage of this little quirk, and we're going to use it to solve the answer that we're trying to find out, which is what is the percentage of total for a particular sub-category of the category when we're only looking at one sub-category, okay? Let's switch back to Tableau and have a look at that question. Let's just clear the annotations off the screen here, okay? So, I'm just going to keep Chairs into the visualization, and what we're going to do is we're going to open up a new calculated field, and if you've never looked at LODs before, that's fine. Um in all my function videos, I've actually been highlighting the fact that you can look at any function and see how it should be written and how it's used just by opening up this little side pane here on the right-hand side and essentially going to that function and seeing how it should be used. In this case, we're using FIXED, so let me type in FIXED and select that right there. You'll see that actually, here on the right-hand side, it it has a very sort of strange notation. It's one of the functions that actually uses a curly bracket to start it off. Um then you have to declare your dimensions that you want to basically target in terms of your Level of Detail, and then the aggregation, and then you close it off essentially. So, let's have a go at writing a calculation. I'm just going to write it first, then I'll explain it after I've written it, okay? So, let's just first make this larger so you can see it very clearly. And I'll just uh go in here, and I'll type uh curly brackets to open the Level of Detail. I'll then type FIXED, and then what we're essentially asking Tableau to do is to control the Level of Detail based on the dimension we're about to declare now, okay? So, in this case, I'd like to do this for the entire category. I basically want to know what the category total is so that I can use that in my calculation, okay? So, fixed to the Level of Detail of Category, I want you to go and calculate the sum of Sales, and this is actually no different to what we would have normally done. Uh let's just do that there, close off the curly brackets, and then do one more uh just to close off the entire LOD, okay? So, you can see here that this LOD is actually valid. You can see that right here at the bottom. And so, now, the key thing here is that essentially, we need to sort of just walk through this and understand what is going on, okay? So, let's do that. The first thing I want to do is just sort of highlight the notation for an LOD, okay? The first thing you have are these curly brackets at the beginning and the end. These essentially open and close an LOD calculation, okay? Now, depending on the LOD that you write, the next bit is actually about telling Tableau what kind of Level of Detail you'd like to run. Um in this green section here, you'll see that it says uh FIXED at the moment, but you can actually have two other types. In this video, I'm only looking at FIXED. The other two types are INCLUDE and EXCLUDE, okay? Now, the next thing that comes after that is actually this, which is essentially us declaring the Level of Detail. And in the context of FIXED, it's nearly always something that can either be in the visualization or can be outside of the visualization Level of Detail. And the unique thing about the FIXED Level of Detail is that it acts independent of what's happening in the visualization, unlike INCLUDE and EXCLUDE, which take what's in the visualization into account. FIXED is the only Level of Detail calculation that acts independent of what's in the visualization, even if it's at the same Level of Detail as the visualization, if that makes sense. So, you'll see here, we do actually have Category in the visualization, but this calculation is independent of that Category. If we were to take it out, it would still behave correctly, okay? The next thing we need to do is essentially our aggregation, and that's what we have here: SUM of Sales. And this is actually just what we do normally. It's the same way we'd write any calculation, okay? So, we have these constituent parts of an LOD calculation, and essentially, they help us tell Tableau how we'd like the aggregation done and at what Level of Detail we'd like it done at, okay? And so, as I've said with the FIXED one, essentially, we're controlling this at the Category level, and this is going to happen independent of the visualization. And lastly, it's going to happen it's going to happen before this filter here, which is a Dimension filter, so it's going to happen before this filter is done, which means it will keep the context of the total and have the correct number. So, let's go ahead and hit Apply. In fact, let me just type in FIXED LOD here, and let's just hit Apply and click OK. And now, when we drag the FIXED LOD into the view, you'll see something new, okay? Remember the number that we saw earlier on? It was 742,000. Now, just to clarify that, let me go ahead and remove this Sub-Category fi- filter for Chairs, and if I click on Furniture, you'll see that this uh SUM now says 2.9 million. Well, that's because it's adding everything in here, and it's sort of kind of going crazy. So, let's just clear the um let's just clear the table calculation that was in there, and let's just select these values here, so 1, 2, 3, and 4, and you can see it's 742,000, and that's the exact same value that we've got here, okay? So, now, when I just keep Chairs in my view, notice how Tableau remembers the value, because essentially, it computed that number before it filtered it out of the data set. And so, that's the really important thing here: it's doing that calculation, and then it's keeping that Level of Detail in context essentially as we do other computations. So, this makes it very easy to always start telling a story about your data set at different levels, and also sort of relating your numbers to different sets of context, okay? Um now, let's go ahead and finish this calculation to actually find out what the percentage of total should be. Before, um you saw this uh actually changed to 100%. So, if we go back here to uh Percent of Total, you can see that it's operating at 100%, which is, of course, incorrect. We actually specifically had it using the pane, so let's just keep that at the pane so we can see that it's working correctly. And now, let's finish our calculation. Uh in order to do this, I'm going to create a new calculation field, and in here, I'm actually going to bring in the two things that I've used before. So, the first thing, I'm just going to do is SUM of Sales, and I'm just going to type in Sales here, and I didn't do that correctly. Uh there you go, so there we have SUM of Sales. And if we expand this, uh what we're going to do is we're going to divide the SUM of Sales in the view Level of Detail against the calculated FIXED Level of Detail for the Category, okay? So, this is interesting because I'm not actually specifying the Level of Detail for one of my calculations, but in this other one, I am actually going to be using the FIXED LOD, which will give us the value for the Category. So, this one is actually not listening to the visualization, it's just going to be looking at Category, and I know that here because if I just click in it and I highlight this to you, you can see that this is my FIXED LOD working in the background, okay? So, this is going to allow us to do a couple of things. It means that as my visualization changes, the context and the percentage of total will always be computed against the Category, which is really, really important, because if I start doing crazy things to my visualization, I know this number is going to be correct, okay? So, let's just say that this is going to be um Viz LOD SUM of Sales divided by the Category SUM of Sales, okay? Which will give us a percentage of the total against the category, okay? So, let's just hit Apply, and now, you'll see that this new calculation has just shot all the way over here, and it's now ready for usage, okay? So, let's go ahead and click OK, and let's bring this into my view. So, I'm just going to drop that in there, and you'll see that it says zero. You're probably thinking, "Ah, what's happened here? Why has this failed?" Well, let's think about what we just did. If we edit this calculation, we essentially created a percentage, and because the number's so small, it's essentially rounding it down to zero. So, what we need to do is to change this to a percentage. Let's go to Default Properties here, Number Format, then Percentages, and we're just going to put it at one decimal place here, click OK, and now, you'll see it says 44.3%, okay? Now, this is the moment of truth. I'm going to remove the Chairs from the Filters pane. We're going to see if this 44% matches with what used to say 44% here, but now says 100% because it has no context of the category. So, let's go ahead and remove that. And there, we have it!You get the exact same number. So, it says 44.3 because fundamentally, I've set my uh number of decimal places here to one. If I set it to two, you'll see it's exactly the same number: 44.27, 44.27. So, now, you can start to see the power of an LOD, specifically the FIXED LOD, because in this context, it's essentially allowing us to take a calculation that actually is in the view—we have Category here in the view—but because of the way the calculation works, it's allowing us to move it up in the order of operations, actually keep the correct value, and then have that in context for another calculation that we're going to be using, okay? Now, in this example, that's sort of just one quirk. I'm basically using it to get around the order of operations, but in other contexts, you might actually use it to answer slightly different questions. And so, that's now what I'm going to do: I'm going to show you a couple of other use cases for the FIXED LOD that you might want to use going forward. Let's have a look at those. Okay, for this next one, I'm just going to open up a new sheet, and what I'm going to do is I'm just going to bring in our Order IDs. It's a very common example. If I bring in our Order IDs, and um the, you know, the key question that I'm always asked with this with this particular view is I always ask what Level of Detail is Superstore uh Sales at, and most people say Orders, which is actually the incorrect answer. The correct answer is actually the Product. Uh specifically, the individual products in each particular order, okay? Because that is actually when you get a single uh one here when you do a count of rows essentially. So, now, this is actually at the lowest Level of Detail just for this Orders table. So, I always say that it's really important to bear that in mind, because with the new data model, each table has its own Level of Detail. So, People is at a very different Level of Detail to Returns, which is at a different Level of Detail to Orders. So, each one of these has a different Level of Detail, and because of the data model, it can sometimes sort of be an abstract thing understanding the Level of Detail for your actual data set when you've sort of blending all these things together. Blending is the wrong term as well—when you're sort of creating relationships between these things, okay? So, that's actually the Level of Detail for our data set, but that's not what we're interested in. What I'd like to know is what was the first order date for any individual customer, and then I want to know for subsequent orders, um, you know, I want to be able to relate that first order so I can tell how many of my customers are repeat customers in the future and how many customers are new customers in the future in in and in any given particular month, okay? So, let's have a look at this. If we bring in Customer Name, let's just bring Customer Name in front in front of Order ID, we'll start to see that some customers have multiple orders. And the next thing I'll do is I'll just bring in Date onto Detail, and what I'll do is I'll change this to a month uh I'll actually change this to a day, so we can actually get the exact day that the order was made. Then I'll drag that here next to the view, it's came. So, I'll just change the Level of Detail for the date there, put it down to the uh day, individual exact date, and then I put that here next to the order. Now, what I'd like to do is actually sort these as well. So, let's just try and sort this uh from uh using the field, and we're going to just use the Order Date here, and we're going to try and sort ascending. Uh let's sort with the MIN if that makes sense. So, let's see: April, May, this doesn't sound like it's working correctly, so uh field, Order Date, that should be correct. So, of course, what I'm doing here is I'm sorting the wrong thing. I'm sorting the individual date and not the individual order. So, let's let's clear the sort, and let's start again over here in Order ID. Uh let's go back to Sort, schoolboy error there by me. Uh go to Field and Orders. Uh let's say Order Date in ascending order starting with the MIN uh if that makes sense. So, yeah, April, May, October, that's correct, that looks good. Okay, good. So, we've now set these orders in the correct order, and what I'd like to know is what was the first order date for each particular customer. Now, you're probably thinking, "Well, how would you do this if you didn't use an LOD?" Well, what you'd probably do is actually you'd create a separate table in another data set, which just had the customer and the first order date. Essentially, that would give you uh like a separate sort of ETL job outside of Tableau. So, you'd go in your data set, you'd basically get rid of all the other order dates other than the first one for the customer, then you'd blend that back into this data set or you'd join it back into this data set to add an additional column, which would then give you the answer. That's sort of the long way of doing it. Now, the quick way of doing it is to use an LOD, of course, because what I can do is I can say to Tableau, "Hey, fixed at the Customer level," okay, so for each customer, "I want you to go and find the minimum Order Date," okay? Let's just make sure we type this correctly: Order Date, okay? And once you've found that, I'd like you just to return it across the whole data set. So, you can see this LOD is also now a valid calculation. Uh you can just see that here at the bottom. And now, we're pretty much ready to go. And what I'm going to say here is I'm going to say "First Order Date for Customer," okay? And we're going to hit Apply and click OK. So now, if I drag that into Detail and then I just go ahead and set that to Day and then set this to Discrete, you can then put that next to the other one, and you can see that it captures the first date and repeats it across every row for that particular customer. So, I now know the very first order date for every single customer in my data set without having to go out and do any particular sort of ETL job, okay? Where this comes handy is if we now build a slightly different visualization. And note when I build this visualization, I won't have Customer anywhere in the visualization, okay? So, let's just go in here, and let's grab Sales, and I'm just going to put this onto Rows, and you'll see you just get one bar over time. And what I'd like to do is show how many of my customers are repeat customers over the years essentially, okay? And so, what I might have done is I might have dragged Order ID, but actually, the thing I want to use here is the Order Date. So, if I drag Order Date into the Columns, you'll see that I get a nice simple line chart. I'd like that as a bar chart, so I can add some context to this. And so, what I'm going to do now with the Color shelf is I'm going to drag that First Order Date, because essentially, what this will do is it will give me the year of the first order date for each and every customer, okay? So, I can actually see let's say if the customer first ordered in 2017, it will mark that one color. If they ordered in 2018, another color, and so on and so forth. But it will continue to do that for all their subsequent orders, as you saw in the table. But I don't have Customer anywhere here in the visualization Level of Detail. So, let's drag that here onto Color, and now, you can see that working in full force. So, you can see here that of course, in my first year of business, every customer was from that year, so 100%, okay? And then in subsequent years, that number has changed. And so, we can even do things like calculate the percentage of total for each year here. I can just go back into SUM of Sales, do a Quick Table Calculation, do Percent of Total, and of course, it will do this from left to right doing table across. So, these numbers here are the totals across the whole data set. I actually want it differently, I actually want it to compute the percentage of total within each year. And so, what I can do is I can do a very quick cheat here and just do Table Down, and that will do it vertically from the top to the bottom of this table, which is just once each year essentially. And then I can actually grab that number and put it on Label. And now, I can confidently tell you that in 2018, 77% of our customers were first-time customers in 2017, and so on and so forth. And so, you can start to see how you can use this for things like cohort analysis, which is a very common type of analysis that you'd like to do, and it hasn't taken me long to do any data prep whatsoever, okay? So, the fundamental thing here is that LODs allow you to do calculations at a different Level of Detail to what's in the visualization. The FIXED Level of Detail is the only one of the Level of Detail calculations that works independent of what's in the view, even if the dimension you're using is actually in the view, if that makes sense, okay? Now, the last thing is that the FIXED LOD allows us to prescribe the Level of Detail, and so, it's really useful for telling Tableau how to aggregate something, or how to calculate something, and then use that in context of another calculation. You've seen me create two versions: we did a very basic percentage of total, and in this one, we've done a very simple cohort analysis uh visualization just using the minimum order date for each customer, doing that as an LOD, and then using that throughout our data set to give us some context for what's going on, okay? So, hopefully, that's been a useful guide for how to use the FIXED Level of Detail, and hopefully, a decent introduction to Level of Detail calculations. Now, these get very complex very quickly, and so, what I'm always going to do at the end of these videos is obviously show you some resources that you can go to to get a little bit more in-depth analysis and an in-depth insight into how these functions work. Let's just go over here to Tableau, and if I just go in here and I type in uh Tableau level of detail, there's a whole world of resources, okay? The first one is obviously the page itself um that Tableau have created for documentation. It's really, really good. It has so much useful context in here. And if you're the kind of person that loves detail and precision, this is the page for you because Tableau detail everything that is involved with Level of Details. And if you go to the very end, if I go to See Also, you'll see that Tableau also link off to other white papers and other articles that you can go off and read more about. The one I'd actually encourage everyone to have a look at is this top 15 LOD expressions. Now, I haven't covered EXCLUDE and INCLUDE yet, I'll do those very soon, but essentially, here, you can see all the different types of questions that you might answer and ask as a business with LODs, okay? So, this is really, really good. If you've just done this cohort analysis here, for example, that's a really sort of common one, but you might also have other examples that, you know, the kind of questions that you've thought, "Hey, uh this is really simple thing to do, right?" And then you've actually tried it, and it's not done exactly what you've expected. That's probably because LODs is what was missing from your sort of knowledge and information. Uh Percent of Total, we just did this one as well. Um that's a very basic one. New Customer Acquisition is a very common one. Comparative analysis, there's also this concept called proportional brushing, where you can show something in context of a slightly bigger sort of uh part as well. Um and so, just check out each and every one of these concepts. If all you did was read about these and just know about them, then you'll know exactly where to come to in the future if you ever get stuck. Now, another thing to bear in mind is that there are some restrictions. So, if I go to the second link here, go to how Level of Details expressions work in Tableau, uh they actually have a little bit more context as to how it's actually doing the calculation in the background. And if you scroll down, it gives you this sort of explanation I gave you before of, you know, the viz Level of Detail and what's going on, and then also, if I keep going down, it also talks about limitations of Level of Detail, so things that you have to watch out for um if you're working with these. Now, the the thing about limitations is you know them when you hit them because you'll realize something is not happening. So, this is the kind of page to just be aware about if you try an LOD and it's not working the way you've expected. The other thing is to be aware of that LODs don't work for every single data source. So, if I just scroll all the way down, you'll see that Tableau actually lists which data sources and databases support LODs and which ones don't. Now, the typical database that you'd use generally do, so you know, Amazon Redshift, you know, Microsoft SQL Server is supported beyond certain versions. And so, essentially, if you're using the latest database, and you're using the most modern version of that database, generally speaking, it will be supported. But for things like cubes, um which tend to be slightly cumbersome to work with and actually lock in that kind of information anyway, you'll find that there's actually a few restrictions there just to be aware of uh and just to to sort of um make sure you look at before you get stuck in. So, I'll put these three links in the description. The first one is this Level of Detail page, the second one, this article about the top 15 LOD expressions, and the last one, which gives you some context about some of the restrictions in Level of Detail calcs, okay? So, hopefully, that's been a useful introduction into the FIXED Level of Detail. In the next video, we'll look at INCLUDE and EXCLUDE, um which are slightly easier, but are maybe slightly more of a sort of a mental mind maze to kind of get your head around, okay? If you've enjoyed this video, you know what to do: like, subscribe, share the video with someone who might find it useful. Um if you've got any comments, please leave them below. I'd love to get your comments, always love the feedback that I get, positive or negative. Um it's really useful context for me and for other people who watch the videos. And as always, I'll catch you in the next video. Take it easy.
