# LOD #11 - How to Percent Difference Against Average in a Range

## Метаданные

- **Канал:** sqlbelle
- **YouTube:** https://www.youtube.com/watch?v=Mbd4C1nkVf0
- **Дата:** 19.05.2024
- **Длительность:** 13:51
- **Просмотры:** 1,517

## Описание

This video covers in depth the 11th use case from the Top 15 Level of Detail (LOD) Examples, based on the Tableau Whitepaper written by Bethany Lyons. 

This use case focuses on comparing the average value over a specified date range to a particular value elsewhere in the chart. 

Learn to create charts that show specific date ranges, allowing for dynamic comparisons of daily values across different time periods.

We will use different calculations, as well as LOD expressions, to calculate the percent difference. 

Practical Application in Business Scenarios:

Financial Analysis: 
Monitor stock performance over specific periods against single-day fluctuations to inform buying or selling decisions.

Sales Trends: 
Compare average sales in promotional periods to regular days to gauge the effectiveness of marketing campaigns.

Inventory Management: 
Assess average supply levels over time against specific high-demand days to better prepare for future demands.

Whitepaper:
https://www.tableau.com/blog/LOD-expressions

## Содержание

### [0:00](https://www.youtube.com/watch?v=Mbd4C1nkVf0) Segment 1 (00:00 - 05:00)

this next video is a comprehensive tutorial discussing the 11th use case in Bethany Lion's top 15 LOD expressions and this 11th use case is where we compare the average value across a range to a certain value anywhere in our chart let's go ahead and start by downloading the sample workbook that comes with this white paper if we scroll down we should be able to see a way for us to download this white paper so right here let's quickly review what this dashboard shows so in here we can set our own start and end dates and that's going to be shown on screen as a reference band what we want to do is we want to be able to take the daily values within this range get a number and compare that number against any daily values that we're seeing for the rest of the chart so for this particular example this is from February 1st 2014 to June 1st 2014 the average daily value in here is $619 so for this particular example we want to compare this value to the value in July 3 2013 which is $10. 23 so essentially what we want to do is we want to take our $10 23 subtract that with 6. 19 and figure out what the percent difference is and to do that we want to divide the result with 619 and that should yield us 65% note that all of these values pertain to a single stock ticker let's go to one of the worksheets we can click on the view data icon in our sidebar let's open this up and let's sort this by the ticker values let's take a look at the ticker fire what we're seeing in here is for every date for every day there is only a single value so for example one value for December 31st 2012 one value for January 2 3 let's go ahead and download the original data source let's use that in a brand new workbook so we can recreate what is being demonstrated in this example and in here there should be an option for us to export the data to a CSV file so let's go ahead and do that so right click export to CSV migrated data and let's call this ticker data let's use this new data source now in a new workbook and let us start by creating a simple line chart with reference lines from February 1st 2014 to June 1st 2014 and this is specific to a stock ticker and this one is selecting vdsi create our line chart let's right click drag date onto column so right click drag selecting continuous date at the very top click on okay this generates our date axis for us and then let's display our adjusted closeth drag that over to Rose this is our very simple line chart now we also want to show this per stock ticker so we can add our ticker to our filters right click on ticker in your sidebar show filter I'm simply going to move this underneath the marks card to save a little bit of space and we can also change this to a single value list so on the top right corner there's an arrow we're going to select single value dropdown and let us select vdsi now let's add our reference band and we can start by adding our two date parameters so in your sidebar on the dropdown create parameter the first one let's call this start date this is a date field and the current value is 2014 and this is February 1st so February 1st click okay now we can create a second parameter by duplicating the first parameter because they're very similar anyway so right click duplicate and then for the second one we're going to change the name and default value so let's click on edit let's call this end date and the value for this will be June so June 1st select June 1 Click okay let's Now display both of these parameters so control click and then right click to show the parameter so those are our two values right now in our analytics tab in your sidebar we can select a reference band so drag over the reference band and in here we want to make sure we are targeting the date and not the whole table not the adjusted close so it's going to be the date and as we make adjustments to the reference line we should be able to see the visual update real time for example we want the band to be coming from the start date and we can see the Shaded area get adjusted let's make sure this goes all the way to the end date as well so on the drop down end date and we're simply not going to display any label so in here right now it's set to computation we're just going to leave this as blank so on drop down none for the label under drop down none I'm just going to make some adjustments with the grid lines we're going to remove the grid lines for now so under format lines we're going to select the grid lines to none for this

### [5:00](https://www.youtube.com/watch?v=Mbd4C1nkVf0&t=300s) Segment 2 (05:00 - 10:00)

demo let's also add one more reference line just so we can easily see what number we're comparing to the average number of the range we're comparing to by default we are going to set this reference line to July 3 2013 so let's add our third date parameter so on the drop down in your sidebar create parameter let's call this selected date this is going to be a date value again and set for 2013 July 3 so 2013 we're going to change this to July and July 3 click on okay now let's call this first worksheet our chart now before moving forward what I'd like to do is to display all the details so that we know exactly what numbers we're trying to get so let's create another worksheet this time around we're just going to call this details and this is just for reference just so we can validate the numbers that we're actually getting so for the details let's display the ticker and we're going to Simply leave vdsi so right click keep only we're also going to display each individual date so right click drag date and we are going to select discrete date because we simply want to display this as a column of values we're also going to display adjusted close so let's drag that over to text now in here note that each of these individual values although it's coming in as the sum of sales it's really just a single value because right underneath in our data source there is only a single record per day per ticker to confirm this we can simply click on one of these values go to view data and in the full data tab we should be able to see that there's only a single value so even though we are showing some of adjusted close it is really just one adjusted close to help us with our validation I'm going to create a calculated field that limits the data to the selected values in our parameters so on the drop down I'm going to create a calculated field simply going to call this within date and again this is just for checking purposes and in here we're simply going to check if the date is greater than or equal to our start date and our date is also less than or equal to our end date so drag that over and date by the way in here all of these white spaces it's mostly for readability it's not going to affect your Expressions so we can simply add some white spaces as well to make our Expressions more readable so let's click on okay and let's add this new field into our filter so in here only the ones that are within the selected date range should appear and the reason we want to do it this way is we want to add our totals again this is only for checking purposes under the analytics tab we can double click on totals and in here we can see the grand total at the very bottom this is the grand sum so let's move this up first so right click on grand total column totals to the top and what we want to do is to display not the sum but the average of all of these values and we can do that here by clicking on our measure selecting total using and instead of total using automatic we're going to say total using average so drop down total using average and now what we're seeing in here is 6186 which should be the same value we are seeing from the original example so how do we convert this into an LOD expression right now what we know is we want to base this expression on a ticker so it's going to be fixed to a ticker now what we also know is this has to be only within a specific range of values and that it's going to be the average of the daily values so let's try to compose this LOD expression so on the drop down create calculated field let's call this average across range let's say per ticker this is going to be fixed to a ticker so fixed ticker and we also know that this needs to be within a range of values so we basically have to say if date is greater than or equal to our start date and date is less than or equal to our end date if this is true then what we want to show is our adjusted close so drag adjusted close over and then end our IFL statement let's close this with our closing curly brace now there's a couple adjustments we can make the first error is because our level of detail Expressions it requires an aggreg ated value but our adjusted close that's not an aggregation so we are going to need to enclose this or encapsulate this in an aggregate function in our case we know that this needs to be an average so we can simply put average around this whole expression so AVG and then put the whole expression in here just make this a little bit bigger so we can see the full expression so this is what we're trying to do we're trying to get all of the individual values and we're trying to average that out and we can see that calculation is

### [10:00](https://www.youtube.com/watch?v=Mbd4C1nkVf0&t=600s) Segment 3 (10:00 - 13:00)

now valid now the other adjustment we can do is this expression is already in another calculated field so if you have this other calculated field you can simply use that or utilize that in this LOD expression so in our case I'm simply going to remove this and use within date so if it is within the valid date then we're simply going to display the adjusted close and we're going to pass all of those values into an average value so let's click on apply click on okay let's just validate what number does this return so dragging that over into our text table we can see that the value is consistently 6186 let's go back to the first sheet now I also want to add our reference line for our selected date and let's also display that parameter control so right click on select a date show parameter this is our default value July 3 2013 let's go to the analytics tab drag a reference line over and again this is going to be attached to a date and let's display the parameter value here so parameter value select a date and we can just display perhaps our value here so that's July 3 2013 and let's click on okay now I'm going to add one more calculated field that gives me the value at this day so in here let's create a calculated field on the dropdown create calculated field let's call this selected value on date let's simply say this is if the selected date is equivalent to the date then we just simply want the adjusted close so adjusted close and then end so let's display this value on the label we can see that this is 1023 which is the same value that we see from the original workbook now just for reference let's add our average across range onto our title as well so we can easily see it so under chart double click our title let's just call this average across selected range and let's display this value so average across selected range let's click on apply it's 6186 now we have a lot of the pieces we need in place what we need is this value for the selected range we want the average value across the selected range and in here we're simply going to subtract these values divided by our 6. 86 so we can generate a calculated field for that so on the drop down create calculated field let's call this percent difference from selected range let's move things around for the expression it's going to be the selected value this is already in our canvas so we can simply drag it over and that's going to be minus the average across the range and in here we want to take this and divide this all by the average across range so let's put a bracket around the first expression scroll to the right and then divide this by the average across range okay let's click on okay let's change the formatting right click drag default properties this is going to be number format let's leave this as percentage two decimal places and let's display this as well in our label so right now the value that we're seeing is 65. 38% and again just to compare with the original workbook we are getting the correct result which is 65% we can definitely make adjustments to our formatting now but import important thing is a lot of the pieces are in place this is fairly Dynamic so if we change the date for whatever reason so let's say January 1st 2014 we can see that the selected range average actually adjusts as well and our percent difference if we select a different value altogether it will just automatically update

---
*Источник: https://ekstraktznaniy.ru/video/52860*