Understanding Level of Detail (LOD) expressions

good afternoon everyone welcome to tableau conference and my session about understanding level of detail expressions my name is bora i'm a product manager at tableau I'm one of the people who created level of detail expressions so I'm happy to take your feedback positive or negative after the session when I'm not working on LOD expressions I have a little side project she's six years old and we're trying to make the next great artist out of her so this is her at the Picasso Museum looking pretty excited this is what she did last night so you could tell we have a lot of work to do but we keep trying this is her doing balloon animals and origami ice cream our latest project is I got a drum kit I'm teaching her how to drum and she would like to play you could be mine from Guns and Roses so back to topic so what is this session all about there are multiple ways of teaching level of detail expressions and you will find several different sessions hands-on or breakout sessions in this conference and they all have different approaches you will find some of them that will take a problem and they will give you a solution to it it could take ten steps maybe five of them morality expression so you know exactly how to solve that specific problem and it will repeat this for different problems but I think first you need to have a fundamental understanding of how these things work how they are they affected by filters how do you combine them with other things so this session is meant to be something that will teach you how they work what happens under the covers so instead of learning a specific solution to a given problem you'll learn all the pieces where you can combine them together and however fashion you want to solve your own problems so before we start first we have to define what level of detail is so level of detail defines the granularity of your data and the data has a granularity the moment it is collected imagine you have a transactional database where each transaction is a row of data that would be the level of detail of that table if you have a sensor that's collecting data that's correct this every second the location of the sensor what it measures and that when it's collected will be the level of detail of that railroad data so let's take a look at an example in this case what you're seeing is superstore data database that we ship and there is order ID as one of the columns by looking at this you can probably tell order ID is not the level of detail of this view because there are multiple order IDs in the view this is because each order ID contains multiple items hence causing replication so if you will like to get the granularity of the data the combination will give you a unique row and that will be the actual granularity data is collected or stored in this case so as you analyze your data you add more and more levels of aggregation at the moment you build your first viz you aggregated the data you drag that sum of sales to get one mark so in this case the level of detail of this visualization is region because we have the region as a dimension in there and since there are four regions in this data set you will see four bars you can go more complex the more measures you add you will get combinations of those so in this case we have both category and region and what you're seeing is each mark is a combination of the two so it could be furniture East office supplies West etc there's more than four since it's a combination and as you can see in this case I put the pills on different shelves first it was on the Rochelle now it is on the marks card so different shelves actually have different kind of effect on the level of detail of your view so even though dimensions affected it matters what shelf they're on so let's talk about how different shelves react to this I highlighted the shelves that impact the level of detail if you drag a dimension on to them in this case you could see pretty much everything is highlighted except filters and pages and pages have a dashed line as opposed to a solid line and the reason for that is typically when you drag a dimension into the view you will see an increase in the number of marks if it is finer grained and what you have in the data source what what you have into this but for pages shelf you typically don't see this because it also hides the extra marks so let's say you have sales that is one mark and you dragged a year of sales on two pages shelf now that value decrees because now it's computing your sales instead of across all of the years just for a particular year as you would expect but if you're on the page 2010 it will only show the mark for 2010 so will look like nothing had happened in terms of mark number changes but under the covers it's actually doing the right thing and here are the dimensions that have no impact on level of detail and the reason for that is one if you drag something on the tooltip you will notice tableau will automatically wrap it in attribute so it becomes a measure it's never a dimension hence it has no impact on the available detail and filter shelf even though it has impact on the results because it is filtering things it doesn't actually affect the level of detail of your view so when you write something like this average sales this is your typical Tablet a Gregorian appeal tableau automatically added some around it you will get as many marks as the dome size of the domain of your dimensions so it will compute at the level of detail of the this visualization I don't think I have to explain that to this audience but sometimes you don't want it to be at the level of detail of the vase you want to have a custom level of detail and that's where LOD a level of detail expressions come into play so you just wrap it up like this fancy animation and this computes at the state level - regardless of what your level of detail for the viz is you could be having category in the view this will still compute at state level because you specifically define at what level of detail you would like it to compute so there are three parts to a level of detail expression and there are three types of level of detail expressions key words first we have the keyword element and we have three different keywords fixed include and exclude and I will spend a lot of time on each of these topics then you have the dimensionality this is at what level of detail you want this thing to compute in this case let's say customer ID then I put my colon and then it will be followed by what we call the aggregate expression where you put your aggregation in this case sum of sales and to make this more readable what we do is we inside curly braces when you look at this it doesn't justify because it's a relatively simple equation but typically a lot of people would add multiple things in there you can have multiple aggregations you can have a chain of if statements we don't actually limit you to just putting an aggregation aggregate in there it has to be an aggregate could be more than one it could be if statements etc so as a result these things could get pretty nasty you can have this really large calculation it really helps to know where it starts and where it ends and those curly braces help you with that so let's start with fixed because it's the easiest thing to understand then we'll expand upon with the other examples so I'll pretend we have a visualization and we have nice blue pills in this visualization or you'll see them on the upper right corner and we'll say here's the calculator feel what does it compute so in this case I have segment and category as two dimensions in my view that's the viz elodi and I wrote the calculation fixed category sum of sales so this will give you some of sales for each category right because we said fixed is very specific you say this is exactly what I want ignore everything else just use this so this is the behavior of the fixed does whatever you want it to do so one problem fixed will help you solve is making dimensions out of aggregations so if you remember my first example before the animation I had something like average sales and I said this will always compute at the level of detail of this which means the behavior of that will change from this to this if I drag it into a field that has a customer ID and we'll compute the sum of sales for customer for Dragon interviewer in a state it will compute at the level of state so before you drag it it doesn't know what it is doing if you look at how we handle dimensions they exist on their own on the data pane on the left hand side they have no dependency on what's in the view right they describe themselves so there's something wrong with some of sales because it doesn't know at what level is gonna compute at a given time we don't know what it's going to return it is a surprise it will change from this to this hence we won't allow you to convert them into dimensions and use them as dimensions so if you yet write this total sales you go to the context menu here you will see that for some of there's no option to convert to something like a dimension because it is not defined by yourself and the data pane you can wall work around this problem and say well how about with an example I want to break this chart down by year of first purchase your a first purchase is an advocate write per customer I want to get the minimum date they purchased and I want to make a dimension out of this because I want to slice this bar by that so all you need to do is convert your calculation write a calculation like this here I'm saying for each customer give me the minimum order date that's the first purchase they ever made in my table and now you will automatically realize that we just moved everything to the result of this calculation two dimensions it's not in measures like sum of sales will always be so we even default and we're realizing what it is that it's the dimension if we don't do this and if you have a fixed level of detail calculation you can always drag it and make it into a dimension to further make the point let's drag it in and see the end result first I dragged it in first purchase date and since it's a date field table table automatically wrapped it in ear cord or whatever in this case year and now my bars are sliced by the first purchase date and what I'm seeing here is if I divide my first event it looks like my early customers are still contributing most to my sales because the blue bar is the largest when newer customers are not contributing that much to my baseline so again go back let's say you want to Bing buy aggregates again if you realized if you want it to be in buy aggregates and if you wrote an aggregate like this you will see there's no bidding option in the context menu either so how do I work around that problem so here create there's no create bin option again LOD to the rescue so I'll just make it into a fixed LOD in this case it's per customer name sum of sales and if I now right click on it I'll see that there's convert to dimension and there's also create bins now I can create bins which I wouldn't be able to do otherwise and I can build a vis like this that gives me this insight on you know how the spending varies between different types of customers I have a lot of customers who don't spend that much money well I have some that spent almost $45,000 another problem LEDs will help you solve is this error I'm sure you ran into this it's probably the most google tablet error of all time like you are trying to do something really simple I want to understand how many days since the first order date right but tablet will say while min order date is an aggregate order date is a row level thing I can't mix the two well the solution to this problem is again use an elevation in this case I'm saying fixed LOD highlighting the important parts here customer name in order date now Tablo knows at what level this is going to compute so it can actually treat this as a row level calculation so we'll compute it for each customer and I think it's important to start thinking about LOD expressions as if they're little tables that tablet joins back in and it's going to join this back at the level of row so for each order that you're seeing you will also have a separate column that says here's the first order so when you ask tablet to make a difference of the two well it's in the same row you have two values that you can take a difference off because the minimum purchase date is just replicating for old values that belong to that customer hence you don't get the error and you can really easily work around this problem and I forgot to mention that this applies to all LOD expressions and then fine print it does not have to be fixed you could do include and exclude as well okay so let's talk about include where things get a little bit more interesting so what we do would include is take whatever is into this in this example segment and add include whatever you specify as part of reality expression so it's going through this example let's assume you have a vision where you have segments in the only dimension into this then you're saying include category sum of sales now at what level would this summer sales compute it will take segments from to this all right then it will include category so you end up with include essentially segment and counted category combinations when you're in computing sum up sales so how does this work because when you do something like include you have this mismatch between the level of detail of the view and the calculation if I go to the previous example now we're saying the view has segments as the only dimension in it so it will have let's say four marks and when you do include category now that you will get results for each combination of the two that will give you a mini table remember all of the expressions are small tables that get your and back in it has maybe ten rows so so somehow tableau has to squish those ten rows into the four marks you have in your view so let's walk through an example on how that works so here's the same example of is level of detailer segment and let's say there are four marks color-coded them and this is your calculation just like the previous example in code category sum of sales and let's say it's segment category because we're including category and it has these six marks and to make it easier our color coded them so like blue at the bottom corresponds to the two blue dots at the top so blue dots have the same segments but different categories at the top that hence a different shade of blue let's put some numbers in to make the math easier to make it understand a little clearer so tableau again has to squish this into those four points somehow it will aggregate right and you will see the moment you drag this out of the expression into the view it will just wrap inside an aggregate in this case doing an average so let's walk through the numbers and see what this average does we'll take 7 and 3 because they're in the same segment 7 plus 3 divided by 2 5 it will take 2 and 0 the same shades and same segment 2 plus 0 divided by 2 1 + 6 + 8 just move down because it's one mark in each case there's no aggregation happening so there are multiple ways of looking at this let's look at it as a table since I said you should think of LOD expressions as tables so this is what I love the expression returns because it has segments category remember we used include it has these columns segmented category and you have sales for each combination and what you have in the view is just this consumer corporate home office and this squishing will happen to get to the end result and here segment and what it will do is aggregation down to the level segment it takes everything in that box that those three rows averages up and goes to that cell that's the average value takes everything in a three green area puts an extra note next cell etc and I know pictures help a lot but if you are you like sequel and you want to understand what happens under the covers and here's a quick peek all of the lod expressions are joined back in via inner joins and sequel you're seeing here in red is the query that is coming from the lod expression it is doing group by segment and categories is that's the level of detail of the LOD expression and then it's joining back to a table that is grouped by only segment that is highlighted in green here at the bottom row and it's doing an average around the results which you can see the other green part at the top to reduce those extra rows into a smaller number of rows so what can you do with this let's start with this example where I am trying to understand not my sales per year but understand how much money my customers spend over time what's the average spending per customer are they starting to spend more money over time we're spending less money on my products and I can do this using an include expression right because I don't have the customer in there but that's probably what I want to have it in there because I already have customer if I want to get per customer purchase per year I can do an include so let's do that I have include customer name now what I'm computing is customer name year combinations because it's an include and I'll apply that and tableau won't do the smartest thing and we'll wrap it inside a sum there's no change because we're doing sum of sums that's the same as doing overall sum but you can just click on the menu and say no the sum is not the right aggregate for this I want to do an average and the moment you do that you'll you'll realize you know how overall spending is changing over time on the left hand side you see that my customers were spending about eight hundred dollars per year per customer and then it went down and the nice thing is I think this is economic crisis when it hit it is also in Salesforce uh sorry superstore datasource that's why it's going down and then the recovery starts beginning and people are spending more and more so this is a really simple application but it is a really common application and the nice thing about this is I can go drill down from years to quarter since all my calculation does is take whatever is in the VIS and includes something else to it it will just now take quarters and include customer and will take months so you don't have to worry about being very specific like fixed does because it will react to what you're doing individually drilling up and down so exclude now that we talked about include exclude should automatically make sense what it is going to do but I will walk through the same example to clarify what it will do so imagine just the same example we have segment in category this time as the pills in our view and I write exclude category average sales and what will this do so I will first take segment and category from the view and then it will exclude category right so and what cancel out you end up with just segments let's go through the same steps and look at this in a more visual way I have this many marks and how do I get to that many marks and here's my visible of detail I have segment in category and again these are six marks I just reversed the same problem it's a turn it upside down and I have exclude category sum of sales which means my calc will have only segment because I'm excluding category from that two values so that's my calculative detail and that is four marks so now my calculation has four marks some have tableau will increase the number to six marks in this case and I put the numbers in to make it easier to follow so you have a 10 286 and even though we'll wrap it in average in this case we're going to do a replication so we'll take 10 and replicate it to the other shades of blue 10 10 and 2 will replicate to other shades of orange because they're in the same a segment and 6 & 8 will carry you over because there's no change and notice that since we're replicating instead of aggregating it really doesn't matter if you say average there you can put min max at or whatever you want and it will still work because it's not doing any transformation it's just replicating the let's do the same thing and look at the tables that are being returned again LOD expressions are many tables that are being joined back in so here's the hello the expression the simple one because we're excluding just segments is a level of detail and dimensions and the sheets are two of them segment in category here we go and what will happen is it will replicate it as you could see it takes the value of the consumer replication to all categories within the consumer segment takes the value of corporate replication at the old categories within the corporate segment and so on and under the covers the way this works is again a sequel query that doesn't inner join in this case we just flip the roles the inner join has a group by segment that's the level of detail for the exclude calculation the outer joint has category and segment since these tables have different number of rows when you do the join and we'll replicate okay so what can you do what exclude let's look at some examples a percent of total is a classic example because your total is let's say the pain and your marks are the values without the exclusion so you can compare that value to the rest of the bars within that pain in to say in this case I'm doing sum of sales the value of each bar divided by exclude subcategory which means it is just the category that whole window and divided by it that will give me percent of total and one nice thing about tableau and LOD expressions is we understand that there is this replication happening so if you do something like a total you will realize that we're not messing up by double counting the numbers so here we have the same number replicating four in each pane you can see 674 thousand nine hundred sixty-six is repeating four times and the next one is repeating whatever number of times so if you actually do a total of all of those you would have a much bigger bar for grand total right this is like looks like this sum of the three in terms of proportion and that is exactly what tableau is doing because tableau is aware of the fact that this exclude calculation is causing this replication so instead of replicating and then summing it up and skewing your results it will be smart about it and say well I know what's going on all this total each of the bars only ones so we talked about all these cases where you say include blah some of sales or exclude something some of sales but we also allow you to do things like include nothing some of sales fix nothing some of sales so what happens when you do that so again same exact example actually I for some reason replaced the category of accounting this time so let's say you have state and county and you do include some of sales and there's nothing there as you would expect this will take whatever is in the view add nothing to it you will end up the same oh it's the same result and here the same thing State County excludes nothing you'll get the same results because it's excluding nothing you're probably thinking well why would anyone do this and the reason for that is remember the error about mixing aggregates and disaggregates right if you want to work around that problem without adding anything Inc include or exclude we can use this hack to work around it will make the message message disappear without forcing you to add or remove anything from LOD so there's a special case for fixed because fixed as you know means whatever I tell you to do do a grenade that level so if you say fix nothing or shorthand for it is just curly brace to sum of sales it will take it as ok I want you want me to provide one value for the entire table this is the same experience when you first sit with a using tableau you drag some up sales you don't have any dimensions you just get one value then you panic like oh what happened to all my data is just only one value so that's the same experience it would give you one value for the entire table so we talked about examples where there was always this overlap you say state county exclude county or add something so there was this overlap where there was a clear join that we we can do but there are cases like we don't block you from doing it you can actually have two different calculations with no overlap so how does that work and what would tableau do so here's an example it's kind of a forced example to explain what it does probably not very realistic but let's assume you're trying to understand your customer spending how much do they spend hide my stores and there's this guy I John in this case he just travels to all of these places and we'll go to California and just get chewing gum go somewhere get a chocolate bar and so your residents are spending let's say $500 a month and this guy is spending 50 cents a month and he's skewing your results and you're like well I want to treat this person add up all his travel spending and pretend that he spent that month that much money in all of the states as opposed to just whatever he spent on the states so here I love the expression is customer and dimensions in the sheet is state and we know in the underlying data there's a relationship between these two columns even if your LD expression doesn't tell us we know what have rows aligned so we'll add this intermediate join to make this work and tableau will actually do this so it's pretty crazy given how simple the expression itself is so first it will say well I have this data and dimensions in the sheet for small of course I'm going to do a join which people cause replication you could see the sum for John is being repeated for all of the states you visited then we have to come back down to state level because that's the level of detail of the VIS and it will do what we did in our exclude examples oh sorry include examples it will just add an aggregate over it to get to that level so we do pretty interesting things under the covers even though we tried to keep the syntax very simplistic on easy to use so when you think about level of detail expressions it's really important to understand the filter sequence in tableau because different expressions are impacted differently by different filters so in tableau we have multiple levels of filters and I color coded them because extract filters decide what data goes into the extract so it's a class of its own then we have datasource filters that run at the very beginning and decides what we get from the data source and then we have context filters dimension filters measure filters and table calculators and table track filters again are a different color because the other ones will impact actual data it will filter data out the last one will hides data points so it's not going to impact on a table calculation because they're just invisible as opposed to being filtered out from that this so here one difference of one thing to think about is actually context filters are not that different from dimension filters we just wanted to add another layer so we duplicated it early on and I'll show you some example to have you promote something that was a dimension filter to of context filter to have it behave differently so if you look at this fixed happens after context filters that means if you add a context filter it will impact the results of a fixed calculation include exclude happen after dimension filters and that means if you had a dimension filter like state and which is a dimension and say I don't want these states it will impact how we compute include exclude results and table calyx why affected by measure filters and the ref lines trend lines box plots are affected by table cap filters and the logic here is since you can use fixed as a dimension by itself it doesn't make sense for it to be impacted by dimension filters because it's a dimension by itself it's kind of a weird circular reference kind of thing so first you should be able to build something before you can use it as a filter right you can't have a measure if it's impacted by medals while you have to build a measure first so you can use it as a filter hence you will see the stacking is something that can use as a dimension happens before dimension filter is something you can use as a measure happens before measure filters so let's take a look at how you can take advantage of this so I wrote this percent of total in two ways one is using fixed category sum of sales for the device for the division the others using exclude as we know fixed is not going to be impacted by dimension filters and everything looks fine so far notice as I filter things my right hand side is not changing its I know still 16 seven sixteen point seven percent is the ratio of the total for accessories but left hand side does something really silly as I filter the ratios are changing and Indiana tells me accessories 100% of accessories which is apparently not something I'm interested in doing so the reason why you're able to do this is that again on the right hand side we're using a fixed calculation where what you're dividing with the total is not being affected by the filters you have so it's always dividing by the total of the entire thing regardless of what you selected on the right hand side so this is a really commonly used trick and this is one example or percent of total and they're cooler ways displaying this as well so here is something that pretends to look like proportional brushing so if you look at this effect it almost looks like I'm highlighting parts of the bar right but what it is doing is I have this dual axis chart I have bars of two different colors and one of the calculations is impacted by the filter the other is not impacted by the filter so as I filter out you start seeing portions of the other bar it's giving this effect of proportional brushing so you could do a lot of interesting things using you know how the filters work with tableau if you understand the filter sequence so now the question is we said dimension filters do not impact fixed calculations and I showed you examples where you check filters nothing nothing makes a difference so what if you really badly want to filter out something from a fixed LOD well remember we said this is where it happens it's impacted by context filters so what you need to do is take the dimension filter that you want to use what fixed LOD and promote it to context filter and this is very easy new UI go to your filter shelf if you see a pill right click on it they will see there will be an option sets out to context and you can just click on it now all of your fixed calculations will be impacted by that filter so one thing we talked about so long so far was using just really simple expressions like here's one expression well actually we don't limit you to writing a single expression you can nest them as many times as you want you can some-something average that and take the median of that however many limits that you want to put but keep in mind these are old joins that are happening in a database at some point you're going to upset your DB admin but we don't impose any limits so when you think about this oh you have to understand the rules that govern inheritance in nest nesting so there are two types of inheritance in table calculations one is the impact of fixed etc because we said fixed has impacts on where this is evaluated and which filters effect that and then the dimensionality itself so in this case if you look at the first one I'm doing a fixed state and nested I'm saying include customer so include as you know inherits from its surrounding if you'd build the Dragunov is by itself it will inherit from this if you put it inside the fixed calculation it will inherit from the parent calculation so in this case it will include state because that's a parent it will be the same as writing state customer it will also inherit the strength of the fixed in this case and it won't be impacted by the filters because the parent is a fixed calculation it will change the effect of include so this is a common question I get people have a lot of trouble with table calculations they don't understand how it works often because it is really confusing and the moment we add in level of detail expressions we keep hearing all I'm glad that I don't have to write table talks anymore do you really not need table calyx so there are a few cases you will still need table calculations and I'll actually give you a diagram of how to decide which way to go in the next slide but if you look at table calculations one special thing they do is these moving calculation is like running sums and we know averages and ranks are set right right but everything that I showed so far is just aggregations it doesn't have any concept of ordering so you can't do a rank you can't do a running sum those are things you can't do with level of detail expressions if that is your problem if you want to running someone rank etcetera the choice the better option will be table calculation so it's possible to do it that way beyond that here is a chart I will try to explain because there's a lot in here and this is from one of our partners and Zen masters from Dina black and the first question to ask yourself is do I want to use the results as a dimension but the answer is yes the immediate thing you want to do is use a fixed calculation because there's nothing else no aggregate you can write in tableau that you can use as a dimension right so that's the first question then the second question would be well do I want to do things like top 10 even though we can aggregate things we can't give things like top 10 as part detail expressions in which case you would use something like sense and the rest of it is more nuanced so it is more of a choice where you want to compute the stuff the question you can ask yourself is do I have do I have all the data I need in my view so let's say you have 10,000 marks in your view and you just want to do an average of everything well you can add a trendline that will rough line that will do that for you if you want to sum them up as a second second later maybe you can do totals if those don't answer your question you could probably do to a table calculation and all of these will compute everything locally so you're not issuing any queries to the database so we fast because there's no round tripping with the database and you're not going to upset your DB admin because we're asking too many questions so that is one way to think about do I have all the data I need in my view if you do you have all of these options if you want to push the database you can do elodie calculations if you don't want to you can use ref lines totals table italics whichever so the other option if you said no to that question I don't have the data in my view as in my example where I did include customer while I only had years in my line chart you probably want to push that data into the day push that question to the database so in that case the answer would be use an LD expression and depending on whether you have to use it as a measure or a dimension if measure is fine you can use include exclude if it has to be a dimension you should use fix again I'll send these slides so you can study them at your own time but that's a kind of high-level overview of how to read this so we talked about all the exciting stuff how it works what you can do with it now the stuff that doesn't work it's like the fine print everybody reads on those radio ads but I'll spend some time on instead of one really fast so what I did here was we had a lot more limitations when we shipped us and instead of deleting them I wanted to cross them out so you could see that we're making progress and things are getting better one thing we still don't do is we don't materialize them and tableau extracts and the reason for that is and it's a common question I yet well fixed is joined at the row level and know this level of detail you materialize things at level of the row level why don't you do it the problem is so far extracts have not been aware of the love detail of your calculations the safety of a row level calculation is it just takes some value maybe multiplies with another new value but it's still at the row level it's not gonna mess up your aggregations but if we took a fixed calculation it's already in aggregate let's say I wanted to sum all your spending and it will repeat for all your transactions your total spending so if I do a sum over that since tableau doesn't know that is already aggregated and you were double triple whatever counting everything because extract just thinks it's real level data so until we add the extracts the capability to understand this is this has a level of detail this is not something that is going to work in tableau so there are things you could use in level of detail expressions and there are things you can't use sets combined fields been grouping a gruesome parameters I think it's been over a year we added them and we can use them as part of all of the expressions but you still can't use table calculations aggregate calculations exploit include etcetera and this is really related to the order of operations because if you think about a table calculations happen locally level of each other expressions happen in database so we don't want to take something that tableau computed here and try to pretend that database will be able to run that if there's a lot of databases won't be able to do table counts themselves and another thing we added which was more recent you can put actually expressions in level of detail a level of detail expressed as dimensionality field in the past you could say fixed field name sum of sales but if you wanted to say fixed a day part day order date a calculated field that will just get you to date a day of that date it will giving you an error you can only refer to a field name so you would have to create a calculation you write you know date part day etc then you go to your LOD and put the name now we can just type in as part of the LOD expression and we won't complain again this is related to how we handle table calculations you can't put them in dimensionality you can all you can't also put them as part of the aggregate expression so you can't do fixed County Index because it's not something we can run inside the database and since we talked about include and exclude and how their behavior is defined by the view because it will add something or remove something from the view it's not defined by its self they're forced to be measures they can't be dimensions since they can't be dimensions they can't be used as dimensionality in lod expressions so a common question I get about level of detail expressions is doesn't work with blending and the answer is yes and no there are two different different types of blending and there are rules on how you can combine these things as part of blending so you can't have blend some data source and take one field from that data source and another field from the primary data source and use them in an OD expression it could be you know both of them being dimensions or one dimension comes from one measure comes from the other you could define things in blended data sources and primary data sources but everything in that expression should come from that particular data source and we have two types of little detail expressions weak sorry data blending we call data blending 1 & 2 which a lot of people actually confuse so I'll give you an example of what the difference is so here you could see I have a blended field and I just named superstore superstorm to make it look like it's a different data set so and I have a well behaving LD calc I have fixed shape mode region sales and you could see that I'm using it on from the secondary data source and everything is fine so I can break this this is data blending 1 let's say I checked that order ID there's a broken link in there I make that a non broken link by clicking there and now I get an error now you switch from data blending 1 to data blending 2 and here's what's happening is I have order ID as a linked field but I'm not using it anywhere into this if I dragged order ID into the visit will start working again or if I uncheck that break down link it will work again and the error message is not that X does n't explain what's going on will be well this is the same error message you would get if you use median here without an LOD expression because we don't do median over a blending but that's the cause because you have a field that is linked that's not being used in the view and here I unlinked it again sum of sales perfectly fine it's working I went back data blending one so let me do a recap and leave some room for questions so what we learned today we learned about different types of ability expressions and how they work and we did things like using aggregate dimensions being as some examples we talked about how they respond to different filters and how you can take advantage of the filters for things like percent of total and highlighting different parts of different marks rules that govern nesting have the inheritance works for the keywords as well as the dimensionality itself and the decision that you have to make whether to use table calculations or a level of detail and how you can think through that problem by yourself so this is my contact information please complete the session survey so I know how well or how badly I did I can do better next year there's a session repeat tomorrow if you have any friends you would recommend this session that I couldn't make it I'll be happy to talk to them and we have some related sessions and some of them are hands-on and marked as such probably they're booked most of the hands-on sessions are booked last time I checked I can you can try a standby and there are some other calculator sessions that I mentioned that followed this other methodology on teaching what things you could do by walking you through very specific detailed examples with lod calculations so I'll go back with my contact info and if you have any questions I think there will be a microphone thank you

Channel: Tableau Software

Views: 48,523

Rating: 4.8633094 out of 5

Keywords: Visual data, Visual analytics, Business analysis, Business analytics, Business analysis tool, Data analytics tool, Data Analytics, Analytics, Analytics platform, Cloud application, Business analytics platform, data analysis, data visualization, business dashboards, business intelligence, tableau, tableau software

Id: kmApWaE3Os4

Length: 41min 40sec (2500 seconds)

Published: Wed Jun 20 2018