An Introduction to order of operations in Tableau

hey it's tim here in today's video i'm going to be taking on the second key concept we need to understand before we look at leds and that is the order of operations now i've deliberately called this video an introduction to order operations because i can't cover everything we could possibly cover with order of operations in the length that i normally make videos so what i'll do is i'll introduce to the concept of order of operations show you an example of how it works show you some documentation of places you can go to to read more about it but also give you a link to some video content that you can go and watch on youtube by tableau that gets into it in a lot of detail that gives you lots of examples lots of different types of contexts that you can sort of get stuck into okay so let's start with order of operations let's get stuck in okay so before i actually start i'm going to actually go to the web page i'm not going to start in tableau and we're going to start on a maps web page i actually googled the order of operations in maps because and the tablet order of operations has basically the same sort of concept essentially the order of operation in mats pretty much dictates the order in which you do certain things in order to get the right number when you do a mathematical calculation now in tableau when you're working with that it actually does calculations in a specific order when it's working with data and so what i've already got open here is another tab that essentially describes the order of operations i'm going to link this page in the documentation in the description below so be sure to check it out and there's also this video here that's referencing the documentation from just a couple of years ago if you right click on that open it in a new tab you'll see it's also another youtube video so it's very easy to sort of go to i'll also do a pop-up on the screen now to this video so you can find out and you can see here it's got lots of really interesting examples uh that you can get to and it's uh quite a recent video in tableau terms it's only from version 2018.2 but many of the things have probably stayed the same and the order of operations hasn't really updated that much now the order of operations simply dictates the order in which tableau processes data okay so the best way to think about it is that it starts from the top and so the first thing it will check is do we have any extract filters and essentially if there's any extract filters it will apply those filters and then the next thing it will check is if there's any data source filters okay and then after checking that it will go to the next thing context filters and if there's no context filters then it goes to the next thing now on this left hand side here you can see these are essentially filters so everything on this left hand side is some sort of filter but on the right hand side we have what are considered sort of calculations so you have sets which are sort of groupings conditional filters which can be applied using a bunch of conditions top n and fixed level of detail calculations happening between context filters and dimensional filters and so the important thing here is that in some instances you'll get into situations in tableau where you could swear you've set up the view correctly but actually you're not getting the right number and some weird behavior is happening that for example it could be keeping um the wrong set of values when it should be returning you you know just a simpler set of values from a subset of your data rather than the whole data set and so by manipulating where you put your calculations in this order of operations you can achieve slightly different things and so it's just good to be aware of all the different devices you've got available to you because i think once you sort of understand this you don't have to understand it in depth you just have to know that it's happening in the background and when a situation pops up you'll instantly look for situations in which you can solve this so um it's really really important to sort of be aware of it so if i keep going down here you'll see dimensional filters then include an exclude level of details we'll be covering these very soon data blending then measure filters then forecast table counts clusters and totals then table count filters then trend lines and reference lines so trend lines and ref lines are absolutely the last thing that are always computed and the very first thing is the extract filters okay so let's hop into tableau and i'll give you a very simple example to show you how this is working in a very different context and you can follow along and set this up yourself in tablet but we're going to sort of take our time with this we're going to make sure we set it up correctly okay so the first thing i'll do is i'll go into superstore sales i'm going to select the second one which is the american data source and we're going to bring sales onto the view i'm going to put it onto columns and that will give us essentially cells going across the screen and then the next thing i'm going to bring is the subcategory out of the product hierarchy and we're just going to put this here like so okay then we're going to filter this like this so we have phones shares storage tables and binders okay now the next thing i'd like to do is to bring in a state so i'm going to go into the state hierarchy here and i'm just going to bring in the state and i'm going to put it in front of subcategories so essentially we can see here a couple of things alabama the top top sub-categories chairs machines and phones okay so those are the top three uh categories okay so let me go ahead and remove uh that again i just wanted to sort of you know highlight that factories that alabama chairs machines and phones okay so let's go back in here and let's just apply a top three filter on just a subcategory so let's go ahead and do that and bring subcategory onto filters okay and then you'll get this sort of filter pane and we can hop into this go to the very end select top select top three and it's going to be using the top three based on sum of sales to return just the top three values okay then i'm going to hit apply and you'll see here that phones chairs and storage are actually the top three now depending on how i've set this up and depending on the kind of question i'm working with if i then go and bring the state into this filter one thing you might do is you might expect the top end filter to work even if i've got alabama selected so what we should expect to see is that machines should at least be in the top three in alabama because we validated that right before so let's go ahead and click apply on alabama and you'll see here that the orders change but we don't get the right top three we get chairs phones and storage but if we go to our view again and let's just very quickly build like a validation data set let's uh bring state in in front of subcategory bring cells onto columns keep only alabama and sort this we should see chairs machines and phones so why is it that on this shop the top three items are chairs phones and storage and on this shop it's chairs machines and phones even though we've got alabama in the filter and we've got category in the filter as well just selecting the top three well the issue here is order of operations and so if we think about what we've done here if we open up this filter we're using a top n calculation in here okay so we're using a top three calculation and we're using it in the filters pane okay so the way this works is that the top end is actually at a higher level of the order of operations compared to a dimensional filter for the state of alabama let me show you this in the order of operations diagram so if i go in here you'll see that i have context filters set conditional filters and top end so right here this is basically what's going on this top end function is happening before my dimensional filter for alabama okay and so what we need to do is to somehow move our dimensional filter for alabama above the top end function so that it first filters for alabama then it calculates the top three in the particular setup that we have okay and so we've got a couple of devices to do that essentially look at where our dimensional filter so our dimensional filter i'm just going to highlight where it is in green it's here so essentially we've got some options and we need to move it further up this order of operations and so the very easiest thing we can do if we're only interested in alabama is we can just add alabama to one of the extract filters or the data source filters now i'm not doing an extract in this particular view we didn't take an extract so for this one i'm just going to revert to using a data source filter to show you that this works so let's go back into the view and let's kind of clean out this and let's close this and so i'm not going to change my subcategory top end we're just going to keep that as is and then what i'm going to do is right click on the data source go to my data source filters and then i'm just going to filter to the state alabama so let's go get the state field uh select okay just select alabama click ok and then click ok again and now you'll see its chairs machines and phones okay so now the data source filter is applying the filter that we were applying here above the top end function so i can actually safely remove this state alabama filter and you'll see nothing changes because the filtering is now happening above our top end calculation so if i just go back here to our view let's annotate annotate it one more time our top end function is here and now what we've done is we've moved our alabama filter into the data source filter okay the next thing we can do is we can remove it from the data source filters and i can actually put it inside of the context filter this is another type of filter which just allows you to get some separation between the dimensional filters and the data source filters the problem with the data source filters is it removes everything from the data source so you can't look at any other aspects if i wanted to look at another state i wouldn't be able to look at it essentially because i've removed it from the data source that i'm working with it hasn't been deleted it's just not available in the queries when tablet looks at it because i've put a filter saying don't do that so the context filter is a little bit more powerful because what it allows us to do is it allows us to essentially remove alabama from this view but just in this particular sheet i'm actually able to specify that alabama should go above the top end function okay so let's go ahead and select state and place that in there select alabama click ok it's exactly the same as before we get the same wrong answer as before but this time i'm going to right click on alabama select add to context and you'll see that it goes gray so it goes gray because it's now a context filter so context filters are typically gray and what that actually means is that tableau is first applying the context filters then applying any other top end functions then applying other dimensional filters so if we exit out of this again and go back to this view you can see that now what we've done is we've applied a context filter here and then we've got our top end function happening after the context filter and that means we now get the right answer if we filter to alabama inside of the filters pane okay and so this is order of operations working essentially we're manipulating the point at which a calculation is done to help us get the right answer to the question we're asking and so often a lot of the times what will happen is you'll be sort of getting frustrated in tableau just going look why is it just not doing what i wanted to do and actually it's doing exactly what you wanted to do it just has a set of rules that you have to follow and that is essentially the order of operations okay now i can't stress enough this diagram should be like the holy grail like in fact if you've never heard of this concept for the next week set this as your wallpaper honestly because it's that important it's going to be the thing that you realize uh has made some of your visualizations in the past just really difficult to work with or actually it's made the performance of some of what you're doing really really slow because you've not realized that actually you can move certain filters higher up the data source so they're not even computed later on and vice versa okay so it's a really important concept just to start to understand and table i have great documentation on this i'm always going to be praising tableau's documentation because i think it's actually really good i use it myself i literally read it i learn as much as i can and then i come and make videos for you guys so it's really really good content and some of it has been around for ages you can actually look at these images you'll know these are done in a very old version of tablet i think i'm going to call this as version 7 or 6 of tableau because it's just before the new interface was designed so this is actually the version that i learned tableau with version seven so um this documentation has been around for a really really long time but it's frequently updated so here it's been updated for 2020.4 and as and when they add new features especially when we look at things like the data model it's really important you keep tuning in okay so once again i'm going to stress a few things on this page and be sure to check out the video here it's got a nice really long one-hour session that goes into lots of examples it's a little bit long it might be a little bit dry but it's a really fundamental concept to get your head around and then the next thing you want to do is really get familiar with this diagram this is really important to sort of get your head around and lastly i'm sure i'm not the first to do a video on um context filters so be sure to look in youtube for other people who've done great videos on context filter they've mostly been at conference if i'm honest so look out for tableau conference sessions on context filters and level of detail calculations in general and then you'll find lots of information about order of operations and the other sort of things that are related to that as well so it's a really important concept um just get stuck in but the reason i'm doing this video is so that we can now start to do lods um starting tomorrow so um hopefully you now understand this now when i go through lods i will go through this again so don't worry i'm not going to leave you hanging i will reinforce this as we look at the lod videos but that's it for today um if you've enjoyed this video you know what to do um hit the like button subscribe do whatever you want to do let me know what you've enjoyed about this content let me know what you didn't like as well in the comments below and i'll catch you in the next video

Channel: Tableau Tim

Views: 1,722

Rating: 4.9642859 out of 5

Keywords: tableau, desktop, salesforce, 2019, 2020, version, tableau prep, tableau online, tableau prep builder

Id: KbKSzD3okrQ

Length: 14min 3sec (843 seconds)

Published: Thu Mar 18 2021