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
Tableau | Data visualisation | Analytics