LOD #9 - Investigate First and Last Day of Period Values #Tableau Level of Detail #calculations

for the ninth use case we will create a monthly sales graph however in addition to monthly sales we will also extract and display sales on just the first day of the month last and also display the difference between the first and the Last Day sales as a reminder LOD expressions or level of detail Expressions allow you to control the granularity or the level of detail of your expression we usually resort to lods when our viz or our charts have different levels of granularity than the calculations that we need if you need an LOD Expressions refresher please check out another comprehensive tutorial I have on this topic it starts from the basics and goes through the different types of lods and the implications of the order of operations with lods I've also provided a link in the card above and description down below let's start by creating our monthly sales chart right click drag order date onto columns select our continuous date value for month fourth from the bottom let's drag over sales onto rows now let's duplicate this view as a cross tab right click duplicate as cross tab now what we're seeing in here is the full month and the sales for the full month what we want to be able to do is to extract just the first day of the month the last day of the month and the sales for each of those days there is a way to do this using level of detail Expressions we can use the include LOD so let's create a calculated field let's call this first of month let's add include now since the detail that we really want is still within this particular date we can simply use a curly brace and then include with no Dimensions with it so curly brace include just a colon and for the first of a month it's simply the first day that we have in our data source for this particular month so Min of order date let's click okay and display this let's right click drag display the month so what we are seeing in here is the breakdown of the first record the first date in that particular month January 2018 does not start with January 1st and all this means is we don't have a record for January 1st or January 2nd we can validate that by looking at the details so click on this view data let's go to full data and if we sort by order date indeed the first record has a January 3 date let's close this we can use a very similar Logic for the last date of the month and what we really mean in here is the last date based on our records so let's duplicate this and let's edit let's call this last of month and instead of minimum it's simply just going to be a maximum let's click okay let's display this as well so right click drag and let's display this so we're seeing in here the first day of the month based on our records the last using these dates we can extract the sales for the first day of the month and the last day of the month it's simply an if statement so let's create that calculated field so drop down create calculated field let's call this first of month sales so this is simply if order date is equal to the first of the month then we simply want sales then sales let's click okay we're going to do something similar for the last day of the month sales another calculated field last of month sales I'm just going to copy over the same expression and we are simply going to change first of month to last of month click okay so let's display these first in our charts go back to our chart let's take our first of month sales we could create a shared axis chart by dragging last of month sales onto the existing first of month sales axis so drag this over we can make adjustments to our caller later on if we need to now since we already have our sales for the first of month and last of month it's going to be easy for us to calculate the differential between these so one more calculated field let's call this first and last day difference and this is simply the difference between our first of month sales minus our last of month sales let's click okay and now we can display this so drag that over we can make adjustments to our Mark we can make this a bar chart so selecting this pill and activating that Mark's card we can change this from automatic to a bar and we can also use this measure to determine the color so we can have a diverging palette where you have different colors for positive and

negative values so control drag onto color and this caps our ninth use case