An Introduction to order of operations in Tableau

0:00Hey, it's Tim here. In today's video I'm

0:01going to be taking on the second key

0:03concept we need

0:03to understand before we look at LODs and

0:05that is the order of operations. Now I've

0:08deliberately

0:08called this video an introduction to order

0:11of operations because I can't cover

0:13everything I

0:13could possibly cover with order of

0:15operations in the length that I normally

0:17make videos. So what

0:18I'll do is I'll introduce you to the

0:20concept of order of operations, show you an

0:22example of how

0:23it works, show you some documentation of

0:26places you can go to to read more about it

0:28but also give

0:29you a link to some video content that you

0:31can go and watch on YouTube by Tableau that

0:33gets into it

0:34in a lot of detail that gives you lots of

0:36examples in lots of different types of

0:38contexts that you

0:39can sort of get stuck into. Okay so let's

0:41start with order of operations. Let's get

0:43stuck in. Okay

0:44so before I actually start I'm going to

0:45actually go to the web page. I'm not going

0:46to start in

0:48Tableau and we're going to start on a maths

0:50web page. I actually googled the order of

0:52operations

0:53in maths because the Tableau order of

0:55operations has basically the same sort of

0:57concept. Essentially

0:59the order of operation in maths pretty much

1:01dictates the order in which you do certain

1:03things in order to get the right number

1:05when you do a mathematical calculation. Now

1:07in Tableau when

1:09you're working with that it actually does

1:11calculations in a specific order when it's

1:13working

1:13with data and so what I've already got open

1:15here is another tab that essentially

1:17describes the order

1:18of operations. I'm going to link this page

1:20in the documentation and in the description

1:23below so be

1:24sure to check it out and there's also this

1:26video here that's referencing the

1:27documentation from

1:28just a couple of years ago. If you right

1:30click on that open it in a new tab you'll

1:32see it's also

1:33another YouTube video so it's very easy to

1:35sort of go to. I'll also do a pop-up on the

1:37screen now

1:38to this video so you can find out and you

1:40can see here it's got lots of really

1:41interesting examples

1:43that you can get to and it's a quite a

1:45recent video in Tableau terms it's only

1:46from version 2018.2

1:48but many of the things have probably stayed

1:50the same and the order of operations hasn't

1:52really

1:52updated that much. Now the order of

1:54operations simply dictates the order in

1:57which Tableau

1:58processes data. So the best way to think

2:00about it is that it starts from the top and

2:03so the first

2:03thing it will check is do we have any

2:05extract filters and essentially if there's

2:07any extract

2:08filters it will apply those filters and

2:09then the next thing it will check is if

2:11there's any data

2:12source filters and then after checking that

2:14it will go to the next thing context

2:16filters and if

2:17there's no context filters then it goes to

2:19the next thing. Now on this left hand side

2:21here you

2:22can see these are essentially filters so

2:24everything on this left hand side is some

2:26sort of filter but

2:27on the right hand side we have what are

2:29considered sort of calculations so you have

2:31sets which are

2:32sort of groupings conditional filters which

2:35can be applied using a bunch of conditions

2:38top n and

2:38fixed level of detail calculations happen

2:41in between context filters and dimensional

2:43filters

2:44and so the important thing here is that in

2:47some instances you'll get into situations

2:49in Tableau

2:50where you could swear you've set up the

2:52view correctly but actually you're not

2:53getting the

2:54right number and some weird behavior is

2:55happening that for example it could be

2:57keeping the wrong set

2:59of values when it should be returning you

3:01you know just a simpler set of values from

3:03a subset of your

3:04data rather than the whole data set and so

3:06by manipulating where you put your

3:08calculations in

3:09this order of operations you can achieve

3:12slightly different things and so it's just

3:14good to be aware

3:15of all the different devices you've got

3:18available to you because I think once you

3:20sort of understand

3:21this you don't have to understand it in

3:23depth you just have to know that it's

3:24happening in the

3:25background and when a situation pops up you

3:27will instantly look for situations in which

3:30you can

3:30solve this so it's really really important

3:33to sort of be aware of it so if I keep

3:35going down here

3:36you'll see dimensional filters then include

3:38and exclude level of details we'll be

3:39covering these

3:40very soon data blending then measure

3:43filters then forecast table counts clusters

3:46and totals

3:46then table cap filters then trend lines and

3:49reference lines so trend lines and ref

3:51lines

3:51are absolutely the last thing that are

3:53always computed and the very first thing is

3:55the extract

3:55filters okay so let's hop into tableau and

3:58I'll give you a very simple example to show

4:01you how

4:02this is working in a very different context

4:04and you can follow along and set this up

4:05yourself in

4:06tableau but we're going to sort of take our

4:08time with this we're going to make sure we

4:09set it up

4:10correctly okay so the first thing I'll do

4:12is I'll go into superstore sales I'm going

4:15to select the

4:15second one which is the American data

4:17source and we're going to bring sales onto

4:19the view I'm going

4:20to put it onto columns that will give us

4:23essentially sales going across the screen

4:26and then the next

4:27thing I'm going to bring is the subcategory

4:29out of the product hierarchy and we're just

4:31going to put

4:31this here like so okay then we're going to

4:33filter this like this so we have phones,

4:36chairs, storage,

4:37tables and blinders okay now the next thing

4:40I'd like to do is to bring in a state so I

4:42'm going to

4:43go into the state hierarchy here and I'm

4:45just going to bring in the state and I'm

4:46going to put it in

4:48front of subcategories so essentially we

4:50can see here a couple of things Alabama the

4:53top sub

4:54categories chairs machines and phones okay

4:57so those are the top three categories okay

5:00so let me

5:00go ahead and remove that again I just want

5:02to sort of and I'll highlight that fact to

5:04use that Alabama

5:06chairs machines and phones okay so let's go

5:09back in here and let's just apply a top

5:11three filter

5:12on just a subcategory so let's go ahead and

5:15do that and bring subcategory onto filters

5:17okay and

5:18then you'll get this sort of filter pane

5:20and we can hop into this go to the very end

5:22select top

5:23select top three and it's going to be using

5:26the top three based on sum of sales to

5:29return just

5:29the top three values okay then I'm going to

5:32hit apply and you'll see here that phones

5:34chairs and

5:35storage are actually the top three now

5:38depending on how I've set this up and

5:39depending on the kind

5:40of question I'm working with if I then go

5:43and bring the state into this filter one

5:46thing you

5:46might do is you might expect the top end

5:49filter to work even if I've got Alabama

5:52selected so

5:53what we should expect to see is that

5:55machines should at least be in the top

5:57three in Alabama

5:58because we validated that right before so

6:00let's go ahead and click apply on Alabama

6:03and you'll see

6:04here that the orders change but we don't

6:06get the right top three we get chairs

6:08phones and storage

6:10but if we go to our view again and let's

6:11just very quickly build like a validation

6:14data set

6:15let's bring state in in front of sub

6:17category bring cells onto columns keep only

6:20Alabama and sort this

6:23we should see chairs machines and phones so

6:26why is it that on this shot the top three

6:29items are chairs

6:30phones and storage and on this shot it's

6:33chairs machines and phones even though we

6:36've got Alabama

6:37in the filter and we've got category in the

6:39filter as well just selecting the top three

6:41well the

6:43issue here is order of operations and so if

6:45we think about what we've done here if we

6:47open up

6:48this filter we're using a top n calculation

6:51in here okay so we're using a top three

6:53calculation

6:54and we're using it in the filters pane okay

6:57so the way this works is that the top n is

7:00actually

7:01at a higher level of the order of

7:03operations compared to our dimensional

7:06filter for the

7:07state of Alabama let me show you this in

7:09the order of operations diagram so if i go

7:12in here you'll

7:12see that i have context filters sets

7:15conditional filters and top n so right here

7:18this is basically

7:20what's going on this top n function is

7:22happening before my dimensional filter for

7:25Alabama okay

7:27and so what we need to do is to somehow

7:29move our dimensional filter for Alabama

7:32above the top n

7:34function so that it first filters for

7:36Alabama then it calculates the top three in

7:39the particular

7:40setup that we have okay and so we've got a

7:42couple of devices to do that essentially

7:45look at where

7:45our dimensional filter so our dimensional

7:47filter i'm just going to highlight where it

7:49is in green

7:49is here so essentially we've got some

7:51options and we need to move it further up

7:53this order of

7:55operations and so the very easiest thing we

7:58can do if we're only interested in Alabama

8:00is we can

8:00just add Alabama to one of the extract

8:03filters or the data source filters now i'm

8:05not doing an

8:06extract in this particular view we didn't

8:08take an extract so for this one i'm just

8:10going to revert

8:11to using a data source filter to show you

8:13that this works so let's go back into the

8:15view and

8:16let's kind of clean out this and let's

8:18close this and so i'm not going to change

8:20my subcategory top

8:21n we're just going to keep that as is and

8:23then what i'm going to do is right click on

8:25the data

8:25source go to my data source filters and

8:28then i'm just going to filter to the state

8:30Alabama so let's

8:31go get the state field select okay just

8:34select Alabama click okay and then click

8:37okay again and

8:38now you'll see it's chairs machines and

8:41phones okay so now the data source filter

8:43is applying

8:44the filter that we were applying here above

8:47the top n function so i can actually safely

8:49remove

8:50the state Alabama filter and you'll see

8:52nothing changes because the filtering is

8:54now happening

8:55above our top n calculations if i just go

8:58back here to our view let's annotate it one

9:01more time

9:02our top n function is here and now what we

9:05've done is we've moved our Alabama filter

9:08into the data

9:09source filter okay the next thing we can do

9:11is we can remove it from the data source

9:13filters and i

9:14can actually put it inside of the context

9:17filter this is another type of filter which

9:19just allows

9:20you to get some separation between the

9:22dimensional filters and the data source

9:24filters the problem

9:25with the data source filters is it removes

9:27everything from the data source so you can

9:28't

9:29look at any other aspects if i wanted to

9:31look at other state i wouldn't be able to

9:34look at it

9:34essentially because i've removed it from

9:36the data source that i'm working with it

9:37hasn't been

9:38deleted it's just not available in the

9:40queries when tableau looks at it because i

9:42've put a

9:42filter saying don't do that so the context

9:45filter is a little bit more powerful

9:48because what it

9:49allows us to do is it allows us to

9:51essentially remove Alabama from this view

9:54but just in this

9:55particular sheet i'm actually able to

9:58specify that Alabama should go above the

10:00top n function okay

10:02so let's go ahead and select state and

10:05place that in there select Alabama click

10:07okay it's exactly

10:08the same as before we get the same wrong

10:10answer as before but this time i'm going to

10:13right click

10:13on Alabama select add to context and you'll

10:17see that it goes gray so it goes gray

10:19because it's

10:20now a context filter so context filters are

10:23typically gray and what that actually means

10:26is that tableau is first applying the

10:28context filters then applying any other top

10:31n functions

10:32then applying other dimensional filters so

10:35if we exit out of this again and go back to

10:38this view

10:38you can see that now what we've done is we

10:41've applied a context filter here and then

10:44we've got

10:44our top n function happening after the

10:47context filter and that means we now get

10:49the right answer

10:50if we filter to Alabama inside of the

10:53filters pane okay and so this is order of

10:56operations working

10:58essentially we're manipulating the points

11:00at which your calculation is done to help

11:02us get the right

11:03answer to the question we're asking and so

11:05often a lot of the times what will happen

11:07is you'll be

11:07sort of getting frustrated in tableau just

11:10going look why is it just not doing what i

11:12want it to do

11:13and actually it's doing exactly what you

11:15want it to do it just has a set of rules

11:16that you have to

11:17follow and that is essentially the order of

11:19operations okay now i can't stress enough

11:22this

11:22diagram should be like the holy grail like

11:24in fact if you've never heard of this

11:26concept for the next

11:27week set this as your wallpaper honestly

11:29because it's that important it's going to

11:31be the thing

11:32that you realize has made some of your

11:34visualizations in the past just really

11:36difficult

11:37to work with or actually it's made the

11:38performance of some of what you're doing

11:41really really slow

11:42because you've not realized that actually

11:44you can move certain filters higher up the

11:45data source

11:46so they're not even computed later on and

11:49vice versa okay so it's a really important

11:51concept just

11:52to start to understand and tableau have

11:54great documentation on this i'm always

11:56going to be

11:56praising tableau's documentation because i

11:58think it's actually really good i use it

12:00myself i

12:01literally read it i learn as much of it as

12:04i can then i come and make videos for you

12:06guys so it's

12:07really really good content and some of it

12:09has been around for ages you can actually

12:11look at these

12:12images you'll know these are done in a very

12:14old version of tableau i think i'm going to

12:16call this

12:16as version seven or six of tableau because

12:19it's just before the new interface was

12:21designed so this

12:22is actually the version that i learned

12:24tableau with version seven so um this

12:26documentation has

12:27been around for a really really long time

12:29but it's frequently updated so here it's

12:31been updated for

12:322020.4 and as and when they add new

12:34features especially when we look at things

12:36like the data

12:37model it's really important you keep tuning

12:39in okay so once again i'm going to stress a

12:42few

12:42things on this page be sure to check out

12:44the video here it's got a nice really long

12:47one hour session

12:48that goes into lots of examples it's a

12:50little bit long it might be a little bit

12:51dry but it's

12:52a really fundamental concept to get your

12:54head around and then the next thing you

12:56want to do

12:56is really get familiar with this diagram

12:58this is really important to sort of get

13:00your head around

13:02and lastly i'm sure i'm not the first to do

13:04a video on um context filters so be sure to

13:07look

13:07in youtube for other people who've done

13:09great videos on context filter they've

13:11mostly been at

13:11conference if i'm honest so look out for

13:13tableau conference sessions on context

13:15filters and level

13:16of detail calculations in general and then

13:19you'll find lots of information about order

13:22of operations

13:23and the other sort of things that are

13:25related to that as well so it's a really

13:27important concept

13:29um just get stuck in um but the reason i'm

13:32doing this video is so that we can now

13:34start to do lods

13:36um starting tomorrow so um hopefully you

13:38now understand this now when i go through l

13:41ods i will

13:42go through this again so don't worry i'm

13:43not going to leave you hanging i will

13:45reinforce this as we

13:46look at the lod videos but that's it for

13:47today um if you've enjoyed this video you

13:49know what to do

13:51um hit the like button subscribe do

13:53whatever you want to do let me know what

13:55you've enjoyed about

13:56this content let me know what you didn't

13:57like as well in the comments below and i'll

13:59catch you in

14:00the next video