LODs of Fun with Jedi Filters: Level Of Detail Calcs for Data Geeks - Bethany Lyons

all right welcome everyone for those of you just arriving please please come up to the front you're hurting my feelings by all trying to run to the back corner okay so before we get started with the content just a quick show of hands who raise your hand if your friends and family consider your sense of fun to be questionable awesome and so for those of you who didn't raise your hand I just like to remind you that of all the things you could be doing under the Las Vegas Sun including playing with visible you have chosen to attend a session that has in the title a terrible pun a Star Wars reference and data geeks come on all right so second question for you guys who here has used LOD expressions before good that's the answer I was hoping for so for those of you who are new to LOD expressions just want to give you fair warning that this is not going to be the nice easy introduction to what RL Odie's at all this is the we have a Ferrari let's take this thing and make it fly all right so what you can expect to get out of the session today is if you've been working with tableau for a long time you'll have realized by now that probably 95% of the things you want to do are very easy to find in Google and the other 5% of things you can waste days trying to come up with search terms that people use to describe those scenarios so I'm really hoping that you'll leave this session needing to Google a lot less and actually being the gurus on the forum who are creating the the terms that are yet to be determined for those use cases and so this is my attempt here to try and give some kind of general terms to more advanced less search term friendly use cases which we'll get into right away here but essentially the main theme behind all of these exam Bowles is that they're pretty advanced filter' scenarios that are now a lot easier and made possible with LOD expressions so our first example just a nice example also to kind of get people used to just a refresher on why we built LEDs to begin with is an example that uses guided analysis so the idea of revealing at a high level some information about the data at a lower level so this example here is using a superstore for business to business model where our cut we have customers in a number of different industries and those customers buy some some product and what our executive team wants to look at is to make sure that our salespeople are discounting too heavily so we have the total number of units that are sold to each industry as well as the free number of units that they've been given so the discount rate is just free units for per number of total units sold and so when we are as defined by this parameter here as well our senior management considers a discount rate above 6% to be very concerning so you can see here that by it summarized at an industry level the discount rate is well below that threshold for every industry except for agriculture but we probably don't care because in absolute terms it's so few number of items that you can almost disregard that the problem though when you aggregate data to this high level like a discount ratio is that you lose the detail about is there some sneaky customer in there who's getting a really high discount rate and so that's what these orange circles tell you is the number of customers within that industry that are receiving a discount rate above 6% so here you can see that in transportation there's actually three customers who are receiving a discount rate above 6% and so that now takes you to the detail view that shows you who those three customers actually are and this is fake data by the way so if you see people from GM don't be like what are you doing how did you guys get that discount rate this is super strong all right and then you can go back again and and drill into yeah technology to see who's the one guy same thing goes for Dell they're probably here don't ask them how they did this it's not tableaus data all right so yeah that's kind of the the idea here and so seem useful yeah all right so how we do this is we basically need to a granade the discount rate by customer but customers not in our view we only have industry in our view so we would use a level of detail expression to look at that here it is I'll just show you the expression so to a granade customers or to aggregate the discount rate by customers so that's what this fixed customer name does is it will calculate free units divided by total units per customer and if that exceeds the discount threshold then return who those customers actually are and so to build our actual vis what we do here is it's actually just a dual axis chart something like that and I'll make this a circle so that's what gives us that little the dot basically to indicate the whether the customers are above or below the discount threshold and then we can actually just drop that in our view here and let me get rid of let's move this up here and we don't actually want that on color I'll just stick it on detail so you can see that there's a number of different customers here and so then to actually get the to see that the number of customers we can just use a little table calc trick here where we multiply an index function by that and then compute it using that value and that'll just split it out maybe I'll rescale this with a different value and so will maybe not that much but anyway hopefully hopefully you get the idea of how you can do that so just a neat little visual trick along with the LOD expression sound good also just to let you know I see people taking pictures and I often see people taking notes this is going to be available up on the website we're hoping to get it up on the website next week so both the recording of the session as well as the workbook so you can actually go through and you'll have all of these all of these examples by next week so sit back and enjoy okay so our next example is looking at proportional brushing so here we've got a map showing sales by country we've got a line showing our sales trends across time but now what if I want to see Brazil sales versus all of Latin Americans sales well I can now do that if I click on Brazil here what that does now is the line chart is showing you sales in Brazil is the green line across time and then the black line shows you the total sales in Latin America across time so we can actually see what that is relative to the whole and then the percent of total is just so is telling us by month what percent of our Latin American sales came from Brazil and this works if I click multiple countries so if I select Mexico as well that now updates to show that Mexico and Brazil versus the total amount of sales and then the lighter green value will show you the percent of total sales that's from that are not in the selection so you can make that comparison of selection versus what's not selected useful yeah good okay so there's really just one fact that you need to know to be able to do this type of thing here and that's how the order of filters are applied relative to level of detail expressions and so the majority of what you're going to see in this session is based off of this one slide that's one of the great things about tableau is that you don't actually need to have a lot of knowledge to be able to do cool stuff with it personally my memory makes a sieve look like it has a high retention rate so I've kind of just grabbed onto this one thing and try to make it do as a lot of awesome a lot of awesome stuff so the key point here is that fixed expressions are evaluated between context filters and dimension filters so context filters and data source filters they're basically the same thing now they happen first fixed expressions then aggregate whatever data has been included in the context filter and then dimension filters are applied after that so fixed expressions don't respect dimension filters sounds like it's a bad thing but it's a very good thing it's what enables us to to do this kind of proportional brushing scenario so we start off here with our sales across time and so the fixed expression that we're going to use all this does is fix the sum of sales to the month level and months here is just a custom date part that's all so it's the same as what you see in the visualization and you'll notice that if I bring total sales by month into the view here these two things are whoops they are exactly equal for now so when we have no filters applied there they're equivalent and so the different the distinction between relying on the visual level of detail to define the aggregation versus relying on the lod expression to define the aggregation only comes into play when you start applying different filters so if I now filter to Latin America you'll see that the total line did not change and the sum of sales did so it's now showing Latin American sales versus the entire world's sales which is kind of it's not what we want it all actually so what we want is for is to find total sales in Latin America and then filter to a specific country so what do we have to do with this to make the black line show Latin American sales I heard the answer add it to context well done so that's it just add this to context and now you'll see that they're back to being identical because yeah LOD expression fixed LOD expressions due respect context filters and so now if I go down to a specific country like Brazil now that's what's showing you that comparison this is actually just the action filter exposed so the same principle applies whether you're using a dashboard action or a quick filter action and then to get the percent of total it's just a very simple case of being able to of dividing these two by each other and that's it so very simple to be able to implement this type of filtering scenario in your analysis going forward all right our next example here is looking at market basket analysis so if you want to understand what products are commonly purchased on the same order this is a type of scenario that you could use so here this is just showing you the number of items sold by product subcategory but if I now click on paper what that does is it now shows in the dark blue bar shows you the number of items that were purchased on orders that also purchased papers papers paper so 102 binders and binders accessories were bought with paper and that represents 11 percent of total binder and binder accessory sales items items sold essentially exciting yeah I'm very excited about this send an email to a lot of people when I found out that this works oh they're like go away he's not spamming us okay so I'm happy that you guys are happy all right so how we do this you can see that this is actually it's similar to proportional brushing in a sense because the light blue bar is unaffected by the filter while the dark blue bar is affected by the filter so it's this is like ended it's like a super advanced case of proportional brushing the difference here is what we're passing through the filter so I can't just filter on on product that was the opposite of what I wanted to do so if you just filter on product you're going to end up with something really unexpected this one this one here so if we start out with just number of items by subcategory I think I hit save and I didn't mean to before all right so we have our number of items by subcategory so same thing like what we did with proportional brushing we need to get this number but as a fixed expression so that it's immune to the filter and so what that looks like is just fixed to some subcategory the sum of number of items and you'll see that if we put this in the same view let me replace that these things are exactly equal and I'll just hide that access there all right so that's step one and then step two is setting up the filter so that it applies on the order ID level rather than on the product level and so if you're familiar with actions if you want to pass a dimension through in an action filter it actually needs to be in the visualization so what I'm going to do is drop order ID on the marks card here and what that does is it now basically creates a bunch of tiny little stacked marks so the visualization still looks the same but the trick is kind of yeah hidden behind this and so then when you set up your action you'll say in the filter action you're going to pass through not all fields in the view because that would just be like keep only but you're just going to pass through the order ID and so then clicking on paper now displays paper or now displays each category's total sale or sales on orders that bought paper and the reason this works is because this action on order ID is a regular dimension filter and so it's not being applied to that light blue bar and if you wanted to because from a user experience perspective if you don't want to have all of these tiny little marks you can use an exclude LOD expression to say tableau don't do that exclude the order ID order ID from the sum of the number of items and then that will just aggregate by product subcategory and if we turn off stacked marks then we'll get back to the same visualization that we had before but now with just one single bar I wish my colleagues had the same response to email spam okay next example this was inspired by a brand new tableau user who last week asked me can you do this and I had to tell him no because I didn't want to overwhelm him but I can tell you guys yes actually we can do this so here what you're looking at is profit by country sales across time looks pretty normal but what if I want to compare Sweden's Sweden's profit to Australia's profit well now when I click on them it breaks down the sales or the profit by month for each country but if I haven't clicked on anything it aggregates it across the entire world and so by default if you want to achieve that behavior you need to put country on your line chart and so you end up with this spaghetti mess crazy view that has 300 lines and so this is this is set to apply that behavior only if you've selected less than 20 countries and that's parameterize Abul so if I select like loads of countries it will continue to just display the total for those selected countries because it's actually quite an unreasonable thing to have a hundred things on color and so yeah that's permit risible if I set this to be 200 then it would actually break down by country so just a way to make the information more useful all right any idea how we do this nice - yeah part of me count distinct yes yes winner I wish I had a prize to give you all I've got is a half-drunk bottle of water but I'll give you a high five after okay so yeah effectively what we need to do is figure out how many countries have been selected so doing a count distinct of the countries but if I just do count distinct of country that will give me the count distinctive country by month but what I want to know is the count distinctive countries that are in the filter of what's been ticked essentially so I don't want to group that by month I want to look at that across the entire data source and so if you wrap this in magical curly braces then tada it gives you a hundred and fifty so that's the total number of countries that have been 147 so that's a whole the total number of countries that we have in the entire dataset and you'll notice that if I now just select a couple of these countries it's still at 147 because shout it out you guys know because sorry yes exactly you're fixing before a dimension filter so I have to add this to context and now it under it retrieves the number of countries that have actually been selected in that quick filter and so then our condition what that says basically is if the number of countries is less than the drill-down threshold so if for or what is this 5 if 5 is less than 20 which it is then return the country otherwise this is null or as string value you could call it total whatever it just needs to be one single value and so when we put this on color oh I put it on the wrong thing if we put this on color here now it breaks it down but when I pick too many of them it aggregates it across the entire dataset the entire selection all right so our fourth example fifth I don't know I'm losing track we're looking at path analysis now so this is some data from the OECD that looks at foreign aid sent to and from different countries so this map shows you how much foreign aid the United States is sending to other countries and so it sends the most to Afghanistan that's surprising and then if you look at Indonesia or something else it will reverse the the graph and now it shows you all of the foreign aid that's going into Indonesia and so you can see that Australia is giving the most probably because it feels apologetic for all of the Australia Australian tourists who go and drink excessively in Bali okay so how do we actually filter by country here this is what it looks like unfiltered it's a crazy spaghetti mess that's raining down on Africa so that is a song so if we show a quick filter here and then go and actually filter this down to a single country surprise it does exactly what you told it to and it filters down to that single country this is like yeah why why can't you know what I want you to do so this does this doesn't work essentially to create this type of map the way that the data has to be structured is to have one record or two records for every path essentially so you have a record for the recipient and donor country and then there's this this edge ID that connects those two together by having a common ID field so what I want to do is when I've clicked on the United States for example I want to also keep this record for the seychelles because it has the same edge ID as one of the edges that's associated with the United States so to be able to do that I'll get rid of this this one here let's go back to the US just to look at that connection with the seychelles okay and so we need to create a filter condition here that filters at a higher level of detail than the row level and so the inner condition what this does is it's it looks to see if the country is equal to what you've selected then return a one otherwise return a zero so in that example in the underlying data the row with the US would have a one and the row with the seychelles would have a zero and then you take the maximum of that those ones and zeros and then group it by the edge ID so that edge that connects the the seychelles and the u.s. the maximum of 0 & 1 is 1 and so that then allows us to keep all of the edges that are connected to the selected country so if we put this on the filter shelf and say yes now we can see what's connected to the United States so something that might blow your mind a little bit is that and remember I said in the beginning some stuff's really hard to search on Google this problem this path analysis problem is basically the exact same data problem as the market basket analysis case because in both of these examples we don't want to filter at a row level but we want to actually filter at a higher level of detail and so that's why it's useful to just understand how this works and save yourself the time trying to search for that on the Internet in terms of performance the second case the path analysis will perform a lot better than the market basket analysis because in the case of market basket analysis we had to put order ID on the visualization and so that dramatically increases the the result set that's being returned from the database to tableau so if you are doing this on really large volumes of data better to use the parameter and the and the fixed expression it's not as cool as being able to click on the visit self but this is sort of the classic trade-off between performance and flexibility you guys good to keep going all right they fight your hose of information all right this next example here is looking at competitor benchmarking so what the scatterplot shows is the sales for our company versus the year-over-year growth so this is what our company are you can tell this is vastly anonymized data it really did something wrong in there this has this company has more money than the GDP of some countries but anyway ignore that so this shows you the sales of our company for this year versus our year-over-year growth so we're massive we dominate the industry but our growth rate is not enormous when you compare it to some of these little guys here that make no money but they're very fast-growing so what you might want to do then is compare the growth of your company relative to the growth of your competitors and so unfiltered the view on the right here shows you that our company grew eight point eight percent year-over-year in clothes shoes and jewelry relative to the total market growth and the only thing that we didn't grow in more than the market grew is grocery and so if I now want to compare that growth of my company instead of comparing it to the total market growth I want to compare it to the growth of my nearest competitor I can now just click on income corporation and so now it shows me that this is not a little bit less optimistic so we only grew 6% more in books than income corporation grew in books and but they grew forty six point five percent more in clothes shoes and jewelry than we grew so if this is my nearest competitor and my nearest threat that's probably the area that you want to focus on increasing your total market value makes sense okay so how do we do this so the way that the data is structured is you have a column for competitor and that includes the competitors but it also includes our company and then you have a column for product categories and then the gear over your growth is just the sales of this year whatever it's it's the over your growth you guys know how to do that okay so in general it's very easy to compare two columns across the same row it's not easy to compare two rows across the same column and what we need to do here is essentially that because we need to compare every competitor to us but our year-over-year growth is not in a separate column in the same row in a separate column as all of the competitors it's it's super it's kind of Union to all of the other competitors so that's what makes this problem difficult so the first thing we need to do is basically just isolate out of this all these bars our growth right so that's what the first calculation does is you'll see these numbers are exactly the same as these numbers here and we've just given no values for all of the other competitors and so what what I want to do is basically transform this into the case where we are able to compare two columns across the same row that's the whole point of these expressions here so then the second thing we need to do is now take those values and copy them the whole way down across all of our competitors so just to show you what that does it's now it's now just repeating our growth by-product across all of our competitors and so now that the data is in this format now we are able to compare two columns across the same row so it's now very easy to just be able to take the difference in year on your growth between those two which is just that one - that one yep oh no where's the yeah what have I done wrong I might have kicked something under the table sorry it's still long I indeed kicked something under the table thank you [Applause] just need to remember what that was in case it happens again okay so let me go back was it still on at this point okay so the main point here is is now by replicating those values across all of our competitors we've now got two separate columns that are comparable across the same row and this is a very trivial and easy thing to do with calculations we just take that one - that one to get the difference in your over your growth which you can see is correctly calculating here yep question is it better doing it it can only be done using it that this can't be done using table calculations because as soon as so table calculations are calculated on the result set in your view and the way our filter happens we click on income corporation and we've now filtered out our company so a table calculation would not be able to to do this because our company's data disappears so this is something that pre level of detail expressions was the opposite of fun you would have to do crazy data blending workarounds so yeah okay so now to look at what's now hopefully everyone understands the problem setup how you kind of go about thinking about how to solve this and then in terms of the actual calculations what those look like is we're basically just saying here if the competitor is our company then give the year-over-year growth by competitor and by product and if you're wondering why are you using a fixed expression when you're just fixing it to things that are already in your view it's because wait for it this was the second thing that's got a lot might that my colleagues got spammed for when I discovered this is you know everyone least-favorite error oh my god because fixed expressions return row-level results you can just use them to make that error go away alright so that's what returns the value and then the second calc that replicates it across all of the other competitors what we do is then say buy product take the minimum of this value here and so the minimum so by closed for example the minimum of twenty nine percent and all of the null values that you get across all of the competitors is obviously twenty nine percent and so that's what causes this replication to occur across all of the competitors and then the last bit is just a subtraction of the two so that bits trivial but again the reason why this works is because fixed expressions are evaluated before filters are applied so I can I can filter this down to Allied biskits what a name and and then we can still retain the comparison to our company even though our company has been filtered out of the view so it's pretty simple given that that could otherwise be a very hairy problem to solve [Music] [Applause] all right the next one here is looking at actually before we do the the example question for you guys if you have a passport to any country in Asia please stand up okay stand up stand up come on get involved all right cool a lot of people traveled far awesome decision to stay standing up this is an interactive activity okay if you have a passport American passport please stand up oh and that's the next question only remain standing if you have both we have one guy okay so how do we do that kind of filter in tableau we can we can filter by by nationality and the more you pick the more data you get but what if I want to say who are all the people that have this nationality and this nationality and this nationality where the more you filter the less data you get well that's what this example looks at so here we have sales by customers I filtered this to one quarter but now what if I want to see who are the customers who bought in both of the selected quarters so these are all my customers who bought in in q3 and q4 whereas these are all my customers who bought in one of those and so the more things I select the more this data just gets smaller and smaller and I can see that there's now actually very few customers who have bought in the last five quarters any idea how we do this yeah so count distinct you need to be able to figure out how many quarters are actually selected in that quick filter so this is a really similar example to the one where we conditionally break down a line by country so how we actually do this so we've got our customer of our sales by customer name so if I do a actually yeah so if I do a count distinct of quarters and this is again it's just a custom date part at the quarter level and I've wrapped this in curly braces so that we'll find if we turn on the labels here that finds the total number of quarters which is 16 but to find the number of quarters that have been selected I have other because again but I know how to fix it this time all right so how do I make this number turn into a magic four add it to context see it's really easy I told you don't need to know much there we go so now it's picking up the total number of quarters that have been select captured in that quick filter selection and so then if I want to compare that to the total number of quarters that each customer has purchased in then I'm going to fix on the customer level the count distinct of quarters and the reason that Oh order date there we go the reason I'm using a fixed expression rather than just doing a count distinct and relying on the fact that customers in the view is because yes so that we can avoid that horrible error exactly because it's easy to compare them these two things at a row level and so now I can see that the only guy in this view who bought four times is shahid so now it's just a simple case of doing a boolean comparison to see are these things equal to each other and boom we don't really need that anymore I mean you could just filter by this depends what you want to do we can do something like this to compare the top ones in and out of both however you like so you can change the way the views set up we would just keep only the the true values if we only wanted to see the ones that actually bought in all four rather than comparing them to the ones that bought in some of the selection cool yay by the way is that is Keith health rich in the room that's oh that's you so this was actually you filed a support case that Noah Salvatierra yeah that was your support case that gave me this idea so you all of our ideas come from you guys so thanks for that awesome I'm so happy here in the front row as well you didn't shy away to the back like everyone else okay this is I've saved possibly the most exciting example for for last so if I want to drill down from states to cities currently what you have to do is have two separate worksheets and one worksheet filters the other worksheet but wouldn't it be so awesome if you could just do this oh my god let's do that again because that was so much fun but now let's do it for the whole East Coast come on tooltip' and so it works for multiple states as well yay day there might be developers in the room so the louder you clap the more likely they are to building things alright so who has an idea of how this works no not magic I told you there's it's it's kind of the same it's the same technique over and over to do a million cool things it's just ink counts yay and contacts filters so basically this is a dual axis chart between a map at the state level and a map at the city level and so what we do here is similar to the other the other examples we just take account distinct by state and if that's less than 49 in this case you can set this it's permit risible so you can decide at what point you want to drill down or not drill down then return the city and then it's know if the number of states selected is higher than 49 as in we have all the states in the view so then we've assigned a geographic role of City to this and so on the level of detail here instead of having our actual City field we'll put this city field that is sometimes the city and sometimes it snow and then the same is true for the state we say if the number of states is greater equal to the parameter that you've input then give the state value and otherwise this is also no and that has a geographic role of State and so then on our first marks card that's what we display and it keeps that because at the moment I have all the data in the view so now if we layer those on top of each other and if I come in here and do keep only it doesn't do it why yeah you only need to know one thing as we add it to context and then it does do the drill down and to reset it you can actually expose a keep only filter as a quick filter and so then you just go back to all to be able to reset it so this is a really simple trick to be able to do that yay okay the last example this is um this is more for people who are administrating contents to a very large number of users across an organization that has a hierarchical structure and you want to manage the minimum amount of workbooks as possible so who in the room raise your hand if you're more on this mat the content management server management side of side of things cool this is for you so here I have a blank dashboard Oh No but basically in this data set I have a market level data country level data state level data city level data and so what I want is when the CEO logs in he sees data aggregated to a regional level when the vice-president of a region logs in he sees data displayed by country and when a country manager logs in he sees data displayed by state and when a state manager logs in why am I saying he/she sees data no but the CEO is a she forget about that she sees data at a a city level so this is now kind of kind of possible so you know in our server the CEO is his username is the Big Kahuna so if we look at which is the Big Kahuna see she she sees she sees now total profit in sales across the entire company and then broken down by region this would be a really boring chart for someone who's a state level manager because they would just see one bar for just their sales so that's why we want to be able to dynamically swap out what visualization is displayed here so if the vice-president of Africa logs in now they don't even see that bar chart because again it would be 1 bar which is equivalent to this summary value here so instead there with a math that's displayed at the country level and likewise if the director of France logs in they're displayed a map at the state level and then if I'm going to put the Alberta manager because that's my hometown hi mom now we see this at the city level there's my hometown it is very far north and very cold and so now you don't need to today the way that you need to do that is by having four separate workbooks essentially that is still the advisable solution by the way because this is really messy and it can get slow so this is just if you have a really simple workbook and not a lot of data you could think about using this method but if you have really complex workbooks with lots of worksheets and large volumes of data just don't even go near this because behind the scenes what's happening is a magic not magic it's just it's the same this is just like the prior example on steroids there's basically a whole lot of count distinct going on to be able to figure out the level of detail of the data that that user has access to I'm not going to go into this one because it's you can download it and reverse engineer it if you want to do it but it was just to show you the level of yeah the kinds of things that you can use LOD expressions for alright so in conclusion we started off with a very bad pun and we're going to end off with a very cheesy viz I told you at the beginning that there's really only one key fact that this entire session is based on so that's really all that you need to remember that's what's great about tableau is you don't need to have a lot of knowledge you just need to have a couple of couple of you know bits of information about how the product works and then you can just use your imagination to do whatever you want so as you're going through the conference you're probably going to have a lot of information thrown at you don't feel overwhelmed just just remember a couple of key things and you'll be amazed when you go back to work next week at how much you'll be able to do with with those couple of facts so that's really all I had prepared for in terms of content so we'll take the last ten minutes for any questions that you have yeah yeah so the question was when you put the curly braces around a calculation without specifying a key word that is doing a fixed so that's why curl any any time I used curly braces they ignored regular dimension filters because they behave the same way as fixed expressions good question Thanks yep sorry could we actually get a mic over here so just in down there other side by the speaker yeah effective that is a really hard thing to to put a number on you sort of have to you sort of have to test it so that's why I said at the end with that last example use this with extreme caution because it will hurt your your workbook performance to an extent but that's only you'll only start noticing that if you have absolutely massive volumes of data the majority of people are often working off of you know data in Excel and you'll never notice that as a performance problem if you're working on sort of smaller volumes yeah you kind of just have to test these things any other questions Keith yes the or the market basket analysis you mean so let me just so this one here I did not exclude it it's an it's an action oh sorry oh yeah yeah yeah sorry in the in this expression here so the reason that I did that is so that I could retain the some of you know retain this value of 102 because if I didn't have that if I just had the sum of the number of items that sum is then grouped by product category and by order ID so if I turn stacked marks back on you can never see that this is equal to 102 it's just a hundred and two stacked marks on top of each other yeah so the exclude is still it's a level of detail expression it's kind of a workaround so we need to have order ID in the view to be able to filter on it that then basically breaks our sum of sales so the exclude is to bring it back up to the total per subcategory good question Thanks yep yep so the question was can you select multiple on this you can so if I hold on let me see so if I pick office furnishings and computer peripherals so what that now does is it filters on all of the order IDs that had office furnishings or computer peripherals so it's in that sense it's not but it's just the ability to pass through the order ID to do the market basket analysis it's just it's just a different example I think but in that sense it does or the values together yeah another question yeah Oh how do you prep your data to have the edge ID so how I did this so yeah the the file that I originally downloaded had a origin and destination so I first added a record ID and then did a pivot and so by adding a record ID that means that the origin and destination end up having the same when you pivot it they keep the same value and you use that as your edge ID they were columns in there yeah so so for the path analysis case probably you won't have your data natively in that format you need to pivot it so that you have instead of having an origin and destination column you have a single column for country and then an identifier that says is it the origin or is it the destination and then another column that that pairs together yeah things that are on the common path yep yeah [Music] yep a different thing yeah so I actually did that last week I was at a large company that sells pizza that you've probably consumed in your life they have a number of franchisees and then their franchisees have a number of stores and those stores are in different locations so they have in London which is where we're based they have a couple they have a number of franchisees in London that have different stores and so the franchisees want to be able to compare how their store is doing relative to first of all the total average sales across the old franchisees in the country then they wanted to be able to compare it to the total sales or the average sales across stores in their region as well and so yeah we actually did exactly that we used level of detail expressions to be able to do that and what was also awesome about it is that the security was set up so that when a franchisee logs in they only see their stores but the average still includes the data from other franchisees who have been filtered out via the row level security so it's it's another case of something that you probably would have struggled to do before 9 1 Ford I know there were there no parameters I can show you after if you want any other questions yeah well so they're different in the interface but they're essentially from a query perspective they're basically the same there's them there's a talk Scott Sherman and Renault Reid are doing a talk on the query pipeline so go to that if you want to know more details yeah yeah [Music] I would I would definitely recommend doing so it's a lot easier to maintain than blending because with blending you can accidentally turn off a link and you don't know what happened whereas here you're explicitly defining your aggregation so yeah it's a lot easier to manage in that way it's hard to say because blending it depends on the the cardinality of the blend field so if you're blending on something like product subcategory that has ten items blending is going to be pretty fast if you're blending on order ID that's another story yeah so again you kind of have to test it all right do we have time for one more question all right yeah yeah so the question was other than fixed are there any or wait are there any other expressions that do you mean in the order of filters no just fixed so include an Xcode happen after dimension filters so if you need to mess around with filters you basically always have to use fixed expressions all right I guess that takes us to the end thanks very much for coming and see you at the party tonight [Applause]

Channel: Tableau Software

Views: 28,179

Rating: 4.9261537 out of 5

Keywords: 4688232775001, tc15

Id: 4jOAGqrAuxc

Length: 61min 7sec (3667 seconds)

Published: Mon Dec 05 2016