The Rank function in Tableau & all its variants

0:00Hey it's Tim here. In today's video we're

0:02talking about the rank function. In today's

0:04video I'm

0:05going to cover all the variants of the rank

0:07function and also tell you generally how

0:09the

0:09rank function works with all the settings

0:11and all the different variants of it you

0:12can go through.

0:13That's rank, rank modified, rank dense,

0:16rank percentile and rank unique. Okay let's

0:19get stuck

0:20in. Okay to get this done right we need to

0:21do some setup first. So I'm going to go

0:23ahead and

0:24connect to the American Superstore. I'm

0:26going to go ahead and click the second icon

0:28here and you'll

0:28see that it loads it up nice and easy very

0:30quick. Now the thing we want to do is build

0:33up a list of

0:34manufacturers and the number of products

0:36they have. Now if you can do this off the

0:37top of your head

0:38go ahead get it done but otherwise I'm

0:40going to go here to the product hierarchy,

0:43drag manufacturer

0:43onto rows and then I'm going to drag

0:45product name onto detail. This is because I

0:48want to count the

0:49number of distinct products that

0:50manufacturer has. So I'll drag it onto

0:52detail. The reason I'm doing

0:54it here is because it gives us a little bit

0:56of context and when you put things on

0:58detail

0:58I always sort of treat it as like a safe

1:00space to work with it before you then put

1:01it in the view.

1:02So I'm going to go ahead here and click on

1:04this little drop down, go to measure and

1:06then select

1:07count distinct. I could have written a

1:08calculation to do that but I've just done

1:10it here in the view

1:11very nice and simply for you to follow. So

1:14let's drag that onto where it says ABC,

1:16show me will

1:16kick in and when I drop that in there you

1:18'll see that we now start to get numbers.

1:20Essentially we

1:21are seeing the number of products behind

1:23each of these manufacturers. Okay for the

1:26next step I want

1:27to keep just the top 30 or top 40

1:29manufacturers in this list. So what I'll do

1:32is I'll actually go down

1:33to this drop down on the right of

1:35manufacturer, select sort and then I will

1:38sort it in descending

1:39order using the field that's ready in the

1:41view. Tableau will give a guess that that's

1:43count distinct

1:44that I'm interested in and it will go ahead

1:46and do that and so you should have other as

1:48the top

1:49manufacturer along with Xerox, Avery, New

1:51ell and so on and so forth. That's pretty

1:54much sort of the

1:55setup. Let's go ahead and keep this to the

1:56top 30. I'm going to drag manufacturer, I'm

1:59going to hold

1:59command actually and drag manufacturer onto

2:01the filters pane and rather than trying to

2:03manually

2:04select the top 30 or top 40 I'm going to go

2:06ahead to this top function over here on the

2:08right hand

2:09side, select by field and again you'll see

2:11that it's automatically picked up the

2:13product name and

2:14count distinct. So I can just go in here

2:16and type 30 and now we're pretty much good

2:18to go. We've got

2:19the top 30 manufacturers and the number of

2:22products that each have sorted in

2:24descending order. I'm

2:26going to keep it sorted in descending order

2:28because we want to see the rank is working.

2:30Okay let's get the rank working. Okay to

2:32set up the rank function instead of doing

2:34anything in the

2:35view when you get good at Tableau you do a

2:36lot of things by just double clicking in

2:38the shelf or

2:39double clicking in the pill and doing

2:40calculations but whenever you're teaching

2:42people Tableau I like

2:43to not do that because people want to see

2:45how the calculations are done. So I'll open

2:48up the

2:48calculation window and like I've been

2:50showing people in all my Tableau function

2:52videos if you

2:53go over here to the right hand side you can

2:55obviously like list all the different types

2:57of

2:58calculations that you've got. Rank is

3:00actually a table calculation so if I go to

3:02that you'll see

3:03if I go right down you'll see here are all

3:05the ranks rank, rank dense, rank modified,

3:07rank

3:08percentile and rank unique. For now don't

3:10worry too much about the term table

3:12calculation I'm

3:13going to make another video on this at some

3:15point in the near future. It's quite a

3:17tough topic to

3:18handle so let's try and not make this video

3:20too complex. I'm just going to type in rank

3:22so we only

3:23see the ones that we're interested in and

3:25you can see that they're variants of the

3:26rank function but

3:27they all fundamentally work the same way.

3:30You have the function then you have the

3:31expression that

3:32you're trying to rank then the order in

3:34which you're trying to rank it ascending or

3:36descending.

3:37If I go to rank dense you'll see the same

3:39thing rank modified, rank percentile and

3:41rank unique.

3:42The only thing that's really changing is

3:44the name of the function but they all

3:46fundamentally work in

3:47the same way so I'm going to show you how

3:49it works first with rank and then we're

3:50going to go through

3:51this and do some sort of playing around

3:53with all these different ranks to see what

3:55they actually do.

3:56I'm going to double click rank and what I'm

3:58going to do to make sure that I'm ranking

4:00the correct

4:01thing I'm going to hold command on a mac if

4:03you're on a windows machine it's control

4:05and if you just

4:06drag the count distinct of product name

4:08into this space you'll see that this little

4:10arrow at the

4:10top of the calculation window sort of

4:12jumping around here that's basically

4:14telling you that

4:15it's going to drop it in between those

4:16brackets so let's drop that in there and

4:18then you'll see

4:19that this calculation is now valid. We're

4:21pretty much good to go let's call this what

4:23it is I'm

4:24just going to call this rank normal I'm not

4:27going to name it in a in a too detailed

4:29fashion here

4:30because we're only looking at the rank

4:32function and we're only counting product

4:34names so I don't

4:34need to rank anything else okay so let's

4:37just do that rank normal hit apply and now

4:39that we've

4:39done that we can actually drag rank normal

4:42whilst the calculation view is open by the

4:44way into this

4:45space here and you'll see that show me

4:47kicks in again and it automatically changes

4:49our view to

4:49this table and now we're pretty much flying

4:52this is the rank function working it's

4:54really super

4:55easy to work with and it's really super

4:57simple to understand I'm going to hit okay

4:59so we can close

5:00this function window down we'll come back

5:02to it to figure out how to change the order

5:04in which it

5:04ranks but for now let's close it and give

5:06our table a bit of space so we can see what

5:08's going on

5:09now if you look down this list you'll see

5:10that everything has one decimal place that

5:12's just

5:13standard because of the way the view is

5:14built we can change this later in

5:16formatting I'm not

5:16going to bother doing that right now if I

5:18go from the top to bottom you'll see that

5:20this is counting

5:21in a pretty you know reasonable order apart

5:23from when we get to where we have repeating

5:25values so

5:26you can see here where you have 18 twice it

5:29goes 15 16 16 and then 18 so this gives you

5:31a hint as

5:32to how this works essentially the standard

18:58rank function will do two things it will

18:58count in order

5:39of the largest to the smallest that's

5:41descending by default and anywhere there's

5:43duplicates it

5:44will insert a gap after the item so you'll

5:46see here we go from 14 to 15 to 16 this is

5:49number 17

5:50and then we go straight to 18 so you can

5:52essentially count all the way down the data

5:54set

5:54and the bottom value should sort of

5:56correlate with the last number so if we

5:59keep going down here you

6:00see actually these 12s all share the same

6:02rank but fundamentally we'd sort of be 22

6:0523 24 25 26 27

6:07it's probably 28 here or something like

6:09that so that's just something to bear in

6:11mind the

6:12normal rank function will sort of do

6:13slightly strange thing a lot of people sort

6:16of don't like

6:16this because it's not the way humans count

6:18but this is the way this particular rank

6:20function okay

6:20now let's go back into the rank function

6:23itself I've lost track of where it is I'll

6:26click on edit

6:27and you'll notice there is one more

6:29function a one more feature that we have in

6:31the rank function and

6:31that is sorting by ascending or descending

6:34so let's go ahead and change this to

6:36ascending because of

6:37course the view is already sorting by

6:39descending by default it tells you this

6:41here the default

6:42order is descending so let's go ahead and

6:45just do what it says it says essentially to

6:47open up

6:48a brackets and just type in asc so what I

6:50'll do is I'll just do this I'll just sort

6:53of do this

6:54and I'll just do this asc and you'll see

6:57this doesn't work okay and this is actually

7:00a pet

7:01hate of mine because these brackets sort of

7:03confuse a lot of people if you see this

7:06opening

7:06brackets and this right brackets I've seen

7:08so many people try and type exactly what's

7:10in here and they

7:11get lost so don't do this you don't need

7:12them I'm actually just going to go ahead

7:14and delete this

7:15right here I did it deliberately so you'd

7:16see that you don't need them because a lot

7:18of people follow

7:19the documentation to the letter and they

7:21realize oh it's not working why is it not

7:23working this is

7:24why so here we have asc I'm just going to

7:26close this gap and now watch what happens

7:29when I hit

7:30apply you've got the rank here on the left

7:32hand side I'm going to hit apply and watch

7:34this change

7:35okay it's gone in reverse so now it's

7:37counting from the top to the bottom now

7:41because it does

7:41that and because the sorting here is

7:43already defined by my manufacturer field

7:45nothing changes

7:46so the numbers here essentially changed if

7:49I changed this rank based on the rank

7:51normal

7:51everything would swap around but because I

7:54've got the salt locked to the manufacturer

7:56that's not

7:56going to happen so that's pretty much the

7:58basics of the rank function now what we're

7:59going to do

8:00is look at the other variants of the rank

8:02function and see how they work okay let's

8:04get stuck in okay

8:06we've got the rank normal set up I'm

8:07actually going to go ahead and close this

8:09calculation window

8:10and I'm a bit of a lazy calculation person

8:12I like to duplicate my calculations

8:14wherever I can

8:15it's actually got me in a couple of spots

8:17before because I didn't realize I was dupl

8:19icating so many

8:19things and then ended up sort of getting

8:23confused myself so try not to do that you

8:26can rename this

8:27here I'm just going to rename this here in

8:29the in the view and I'm going to call this

8:31rank dense

8:32if that makes sense so hit okay and then I

8:34'm going to open up this calculation window

8:37just by

8:37clicking edit and you'll see that it's now

8:39called rank dense but I'm now using the

8:41previous

8:42calculation that I was using but we're

8:43going to go here to the rank functions just

8:45by hitting search

8:46and the next one down is dense let's click

8:48on dense and see what it says returns the

8:50dense

8:51rank for the current row in the partition

8:53identical values are assigned an identical

8:55rank

8:56but no gaps are inserted so this is

8:58essentially going to do exactly the same

9:00thing but it's not

9:01going to insert the gaps that we're seeing

9:03you're not going to see this sort of skip

9:05from six to ten

9:06you should get a sort of continuous count

9:09so let's undo the ascending here I'm

9:11actually going to

9:12change this and put it to descending just

9:14so you can see that working although the

9:16default is

9:17descending you can also just type that in

9:19here hit apply and then what we're going to

9:22do is we're

9:23going to change this rank statement here we

9:25're just going to add an underscore dense so

9:27I have

9:27to look around my microphone my microphone

9:29is right here so whenever I'm doing this I

9:31'm looking

9:31around my microphone rather than sort of

9:35trying to dodge my keyboard as it looks so

9:38I hit apply

9:39and now that's ready to go now the reason

9:41you're not seeing any change is because of

9:43course we

9:43haven't brought it in so let's go get the

9:46rank dense function that we brought in

9:48leave it in

9:49there and now we can see that this is here

9:51here it is right next to our previous one

9:54click ok what

9:55I'll do is I'll go to the rank normal it's

9:57a bit confusing hopping around but just

9:59bear with me

10:00let's just type in d-e-s-c in here hit

10:02apply so we can see the ranks next to each

10:05other and I'm

10:06going to change the order of these two just

10:08by changing the order in this left hand

10:10measure value

10:11section I'm going to drag rank dense down

10:13one and you'll see the order changes okay

10:16notice that it

10:17says table down again this is to do with

10:19table calculations but I'm not going to go

10:21into that

10:21and that's for another video it gets really

10:23complex but just know that what we're doing

10:25is we're counting from the top of the table

10:27to the bottom of the table it's as simple

10:28as that

10:29okay so here we are with the rank dense it

10:31goes one two three four five six seven

10:32eight out of ten

10:33and here where you see 15 16 you go 15 16

10:3716 17 18 18 19 20 20 so we actually get to

10:42the bottom

10:42and we have 21 rather than having 26 and

10:45that's because it's not inserting any gaps

10:48pretty

10:48straightforward okay for the next one you

10:51know what we need to do duplicate rank

10:53dense change

10:54that and rinse and repeat so let's go ahead

10:57and do that let's duplicate this and this

10:59time I'm

11:00going to edit it straight away and I'm

11:01going to rename it in the edit pane rather

11:03than anywhere

11:03else and because I've forgotten what comes

11:05next I'm going to look at this up and it's

11:07actually

11:08rank modified so let's go ahead and do that

11:11let's type rank modified in here you can

11:14actually auto

11:14complete sometimes this can be a little

11:16fishy I don't know if you saw what it just

11:18did there

11:18because I deleted that bracket and I'd

11:21started typing right next to my count

11:23distinct function

11:24it actually cleared it so I'm going to undo

11:26that just so you can see that if I have

11:27this brackets

11:28here it won't actually do that so if I do

11:30that and then I go in here and I type

11:32modified then I auto

11:34complete you'll see doesn't delete that

11:36count distinct so just be aware when you're

11:38doing that

11:38if things change in front of you that's

11:40probably why you're not sort of paying

11:42attention to how

11:42it's editing it so here we are with rank

11:44dense let's change this to rank modified

11:46okay modified

11:49here we go I'm really trying to learn how

11:51to spell here you can see that I really

11:53struggle with this

11:54in fact in all my videos I really struggle

11:56with it with this so that's nothing new but

11:58let's go

11:59to rank modified and remind ourselves so it

12:01returns the modified competition rank for

12:03the

12:03current row in the partition identical

12:05values are assigned an identical rank use

12:08the optional

12:09ascending and descending argument to

12:11specify ascending or descending order the

12:13default order

12:14is descending okay so what will this do let

12:17's find out let's find out let's hit apply

12:20rank modified

12:21and let's drag it in I'm actually going to

12:22drag it in here into the measure values

12:24thing you see

12:25when you're working with Tableau it's good

12:27to know all the different ways you can do

12:29something

12:29because then that way when you've got

12:31something right in the middle of the view

12:32and you need to

12:33get on with the task you can just do it

12:34another way so I'm actually going to drop

12:36this rank

12:36modified over here on the left and you'll

12:39see that it actually goes into my table let

12:41's move this to

12:41the right hand side and let's see what it's

12:44doing so let's look at this now we ended up

12:46going all

12:46the way to 30 so what's going on here so

12:48let's go to where we have duplicates here

12:50so we got 14 15

12:5216 16 okay so it actually skips the count

12:55essentially it goes to 17 17 18 20 right so

12:59it

12:59skips 19 and it goes forward so it's

13:01essentially doing the exact same thing but

13:03it's inserting the

13:04gap somewhere else in the in the count and

13:06so we end up sort of running ahead of our

13:09ranks okay so

13:10very very similar you're sort of starting

13:12to get the hint here these things work in a

13:14very sort of

13:15standard way okay now the next thing to do

13:18is essentially keep changing this so what I

13:21'm now

13:21going to do is I'm going to hit okay and I

13:24'm going to duplicate this again and what we

13:27're going to do

13:28is edit it again and the next time we'll do

13:31something different I'll change up how we

13:33create

13:33this just so you can see another way of

13:35writing a calculation this is our rank

13:37percentile now this

13:38is actually slightly different because what

13:40this is doing is it's looking at it in

13:42terms of a spread

13:44instead of instead of giving us a position

13:46it's looking at the percentile spread and

13:48percentiles

13:48tend to work from zero to 100 essentially

13:51zero and one and it gives us sort of a

13:54range in between

13:54that okay so let's go ahead call this rank

13:57percentile and if we just change this to

14:02percent

14:02I can't spell so I'm just going to really

14:05hope auto complete kicks in there we go

14:07rank percentile

14:08descending hit apply that will create

14:11itself over here and we'll do the same

14:13thing and put it at the

14:15bottom below modified and then collapse

14:17this and then move this over to the left so

14:20we can just see

14:20what's going on so this time my face is in

14:22the way so I'm going to move myself to the

14:24right left hand

14:25side I'm just going to be looking the wrong

14:27way for the rest of the video until we can

14:28see this

14:29table but you can see what happens here you

14:32see the top item is essentially in that you

14:35know zero

14:36side of the scale and the last item is at

14:38the 100 percent side of the scale so what

14:40this rank is

14:41doing is it's sort of telling you roughly

14:43where in the know in the spread of your

14:45data you know

14:46things are positioned so stuff in the

14:48middle tends to be around 0.5 so this is

14:50actually technically

14:52the median value if you think of it that

14:54way and then if you think of your you know

14:56the very top

14:56end it's one which is 100 you could

14:59obviously change this to percentages but

15:00you're just getting

15:01a value that tells you roughly where in the

15:03spread of the data this is this is kind of

15:06handy when

15:06you're trying to visualize certain values

15:08and you're trying to understand how they're

15:10spread

15:10across your data but you don't want to

15:12actually have that information encoded in

15:14the chart you

15:15might want it as a color this is how you

15:17could do that rank percentile on the value

15:19and then you've

15:19got some sort of indicator using color to

15:21give you an idea of how the spread is going

15:23okay the last

15:25one is rank unique so let's go ahead and

15:27click okay and what I'm actually going to

15:29do here I'm

15:30going to move my face back because I'm

15:31missing it over here on the left hand side

15:33and let's let's

15:35just do this I'm just going to give the

15:36table a little less space and pull this

15:38down so that the

15:39text can go over more rows so we can just

15:42get this nice and compact and I'm going to

15:44double click in

15:45here okay I'm not going to actually create

15:47this as we have done before I'm going to be

15:49slightly

15:50brave and I'm going to try and write the

15:52whole thing inside of this window okay so

15:54this is a

15:54little bit sort of bold so let's start by

15:56you know let's do the basics first let's

15:58bring account

15:59product name in here so I'm going to hold

16:01command and then drag it in here you can

16:03see I can just

16:04see it inside of that little yellow orange

16:05thing you can see it's in there and I've

16:07just managed

16:08to squeeze it in there and now you can see

16:10we've got the count distinct there I'm

16:12going to type in

16:12rank okay and underscore and then the thing

16:16we need to do is unique now if I was to

16:19type enter

16:20in this place what would it do it would

16:22delete the count distinct so first I'm

16:24going to type in

16:25an open bracket then go back in here type a

16:28u then hit enter and now it hasn't deleted

16:30that

16:31and then all we need to do is go to the end

16:33and we need to close this off because we're

16:35missing

16:35one bracket because we've opened two we

16:37need to close it off with two and now we do

16:39that this

16:40should be working so this was a rank

16:43percentile actually no rank unique I've

16:45just created the

16:47last one here hit enter and boom we have

16:50that ready typed into the table now notice

16:53because

16:53this wasn't typed as a calculation you've

16:55got this sort of weird notation where it

16:58just names

16:58it using the calculation and we also don't

17:00have it here on the left hand side so how

17:02do we make it

17:03go there we just hold command drag it over

17:06here to the left hand side and when we do

17:08that it creates

17:09the calculation for us really really cool

17:12tip there so call this rank unique and hit

17:15enter and

17:16now our rank unique is over there on the

17:18left and it's renamed this here on the top

17:20right so

17:20I've snuck in a little tip there just to

17:22make this a little bit more interesting and

17:25now we're

17:25pretty much good to go so what's going on

17:27here well pretty much everything gets a

17:29unique rank

17:29remember we had 30 rows in our data and now

17:32we have a unique number for everything okay

17:35so we

17:35have one two three four five six seven

17:37eight nine ten all the way down to 30

17:39nothing gets counted

17:40twice so that's pretty much it that's rank

17:42unique that's all the variants of rank we

17:44've gone through

17:45them in a lot of detail now the really

17:47powerful thing we haven't done here is

17:49really sort of

17:50unpick what's going on with the table

17:52calculations you'll see here on the left

17:54hand side you see

17:55these triangles here and these essentially

17:57tell you that there's some table

17:58calculation logic

18:00going on and earlier on I told you that

18:01everything here is working using the table

18:04down direction so

18:05it's essentially counting from the top down

18:07when we do another video on table

18:09calculations you'll

18:10understand that you can actually do

18:12calculations in lots of different

18:14directions in certain

18:15grouping so let's say for example that I

18:17have brought in a category and I put it in

18:19front of

18:20manufacturer well suddenly my ranks stop

18:22working correctly because they're not

18:24necessarily counting

18:26the right thing within each of these groups

18:28so rather than make this video more complex

18:30what I'm

18:30going to do is I'm going to do a separate

18:32video on table calculations where I'll

18:34explain that and

18:34then we'll revisit some of the most popular

18:37table calculations and see how table

18:39calculations and

18:40understanding that affects each and every

18:42one of them okay so that's for another

18:43video be sure to

18:44subscribe to find out more about that in

18:46due course otherwise we've got to the end

18:48of the video you

18:49know what happens here if you've liked the

18:51video drop a comment below let me know what

18:52you'd like

18:53to see maybe next what's the next function

18:55you'd like me to record a video about and I

18:57'll catch you

18:58in the next video