Tableau | Data visualisation | Analytics The Index ( ) function in Tableau
The index function simply counts rows but there's more to it than meets the eye.
- INDEX() counts rows and has no arguments; the quickest way to use it is to type it as an ad hoc calculation directly in a shelf
- Because INDEX() returns as a measure (green) by default, set it to discrete to get clean row numbers as headers rather than a bar chart
- Use Compute Using and the table calculation partition settings to control whether the count restarts within subcategories or runs down the whole table
- Wrapping INDEX() inside RANK() reverses the count, letting you number rows from the bottom up
- A practical date trick: rank an index counting from the bottom, then keep only values 1-6 to always show the most recent six months as data updates
0:00Hey it's Tim here, in today's video I'm
0:01going to be talking about the index
0:03function. Now the index
0:04function is one of these functions that I
0:06promise you once you know about it you just
0:08end up using
0:08it absolutely everywhere. So in today's
0:10video I'm going to be showing you what the
0:12index function is
0:13and lots of creative ways you can use it
0:15including a couple of uses I use nearly all
0:18the time in
0:19combination with other functions. Okay let
0:21's get stuck in. Okay so to get started with
0:23this video
0:23I'm going to start with the American data
0:25set. This is actually going to be the data
0:27that we're
0:27all going to use. I'm going to go ahead
0:29here and select the second superstore sales
0:31here that's the
0:31American data source. So go ahead click
0:34that once and we should be here. Now with
0:36all the functions
0:37what I keep reminding people is that they
0:39are actually all documented inside of the
0:41product.
0:41I'm going to say that every video because
0:43people always forget. So I'm going to type
0:45in
0:46i n d e here and you'll see that the index
0:48function comes up. Now this is essentially
0:50a
0:51very simple function what it does is it
0:53essentially counts rows in your data set.
0:55That's it there's
0:56nothing more to it. That's pretty much it.
0:58The only condition though is that you can
1:00change the way it
1:00counts based on this concept called
1:02partitions. Now don't worry too much about
1:05partitions I'm going to
1:06cover this in a separate video once that's
1:08done you'll see in the link above but
1:10essentially
1:11partitions allow us to group our data in
1:12different ways. So let me show you how the
1:14index function
1:15works then I'll cover the partitions
1:17element a little later. So let's go ahead
1:19and double click
1:20this index function. I'm going to hit the
1:22command plus here just to make this a
1:23little bit bigger
1:24control plus if you're on a windows and now
1:26you can see this is the function. Now this
1:28is it
1:29it doesn't have any expression nothing goes
1:32into it and so it's actually quite uncommon
1:35to type
1:35a calculation called index because the
1:37fastest way to bring it into view is to
1:39actually type it
1:40in context. So I'll go ahead and call this
1:43index here I'll just go ahead and call this
1:45index here
1:46and I hit apply and you'll see that I get
1:47my index function here on the bottom left
1:49hand side
1:50where it's highlighted green but I'll hit
1:52okay and I'll actually show you how I use
1:55index nearly all
1:56the time. I'm not going to bring this item
1:58in but I will show you that it does exactly
1:59the same thing.
2:01Let's go ahead and bring in sales onto text
2:03this will give us the number of sales in
2:06our entire
2:06data set then next I'm going to go ahead
2:08and open up the product hierarchy by
2:10hitting this arrow
2:11and I'm going to bring in category actually
2:13I'm going to bring in subcategory instead
2:15because we
2:15need a much longer list of items and this
2:18is all I'm going to use for this demo. Now
2:21if you just
2:22double click inside of the rows or column
2:24shelf or inside of the marks pane it
2:26automatically opens
2:28this sort of small calculation window you
2:30can just see it there and you can just
2:31start typing.
2:32So because this function is so simple it's
2:34often faster just to do this and once it's
2:37done that you
2:37can just complete the function like so and
2:40then hit enter and you'll see that it
2:43immediately works.
2:45Now by default the index function is
2:47actually a measure because it's actually
2:49trying to count
2:50the number of rows in your data set so it
2:52will always come back and render as a
2:55measure. Now
2:56if you don't know the difference between
2:58blue and green fields I highly encourage
3:00you to go to
3:01Google and have a little search and really
3:03understand that topic because it explains
3:04why
3:05the chart's behaving the way it's behaving.
3:07If I just go down here and I just look at
3:09this you'll
3:10see that accessories has this really sort
3:12of bizarre index value and that is
3:14essentially an
3:14index value of one you can see here it says
3:17index a long table down one. Let me go to
3:19the next one
3:21index a long table down two next one three
3:25four five so it is counting rows but the
3:28way it's
3:29representing it doesn't sort of make sense
3:30because if it was just counting rows you
3:32just want a
3:33number next to the row and so the reason
3:35this is happening is because this is green
3:37and so tableau
3:39interprets this as a measure and therefore
3:41once it's set to automatic we'll actually
3:43want to draw
3:45the bar chart. So let's go ahead and change
3:47this to a discrete item here so you can see
3:50that I'm
3:50changing it and now it switches to a text
3:52representation which in this setup makes it
3:55a
3:55header which makes it a count so now you
3:57can see the index function working as it's
4:00expected it's
4:01a really really simple function and it's a
4:03really nice way of adding row numbers to
4:05your data set to
4:06your tables yes we don't build tables in
4:08tableau it's the most unprofitable sector
4:11but this is a
4:12really useful feature because you can use
4:14it beyond just table. Before we move on
4:16though let
4:16me just show you that this field that we
4:18created earlier here actually works I'm
4:20going to go ahead
4:20and remove this one that we created as an
4:23ad hoc calculation in the row shelf I'm
4:25just going to
4:25remove it from the view and then I'm going
4:28to drag my index from the calculation
4:31windows on the left
4:32now you'll see that it creates exactly the
4:34same bar chart but now that I'm here I'm
4:35going to take
4:36an opportunity to highlight a couple of
4:38things now the reason this is drawing a bar
4:40chart is because
4:42it's using the automatic chart type let's
4:44say that you actually quite like this bar
4:46chart let's say
4:46that you actually didn't mind it then what
4:48you can do is you can change this chart
4:50type and choose
4:51something else let's say for example a
4:52circle you can sort of play around with
4:54that and creatively
4:55you might have a use for this you might
4:57have a use for the fact that the further
4:58down the data set you
5:00go the higher the rank changes and so you
5:02can actually help do things like separate
5:04out your
5:05data set if that was the case or that was
5:07the instance that you wanted let me just
5:09move this
5:09down to subcategory and what you'll see is
5:12that everything sort of goes back down to a
5:14dot but if
5:15I actually go back in here and change this
5:17to subcategory then you'll see that I'm now
5:20doing
5:20exactly the same thing you've got the count
5:22here on the left hand side but now
5:24creatively I've sort
5:25of created this nice separation of data and
5:28I can actually go into the axis edit the
5:31axis invert the
5:32axis if I can do that over here and how I
5:35've sorted my data in order in the order
5:38that we had it before
5:40but I've actually managed to separate out
5:42the marks for whatever creative purpose you
5:45might need so I
5:46just wanted to highlight that because
5:47sometimes you know what Taobao does isn't
5:49necessarily useful
5:50you just have isn't isn't necessarily
5:52useless you just have to adapt it to what
5:54you want it to do
5:55okay we've got a straight away from the
5:57topic here let's go back and let's go back
6:00to where
6:00we were before with the bar chart and what
6:03we need to do now is just set this to
6:05discrete and we're
6:06back to the beginning the other thing to
6:08highlight is that it was talking about this
6:10terminology
6:11called table down let me go back again and
6:13hover on these tooltips you'll see that it
6:16says index
6:17along table down one index along table down
6:20two and that's because earlier on I
6:22mentioned this
6:23concept of a partition and essentially that
6:26's what it's doing it's telling you how it's
6:28looking at
6:29the data set and what partition it's using
6:31it's using the table down partition so if I
6:34go back
6:34forward table down essentially means that
6:37it's looking at this data set as an entire
6:39table and
6:40it's working its way down the list and that
6:42's the direction that it's counting in okay
6:44you can
6:44actually see more descriptions of that over
6:47here but one of the things you have to
6:48understand is
6:49that this is a very complicated topic to
6:51explain clearly so it won't fit into this
6:53video but I will
6:54say is that you can manipulate this to work
6:57the way you want I'm just going to show you
6:59one simple
6:59example okay I'm going to go ahead and
7:02bring in category in front of subcategory
7:05and you can see
7:06now that I have the count going from top to
7:08the bottom still but maybe I want to count
7:11within each
7:12subcategory and so essentially when it gets
7:14to five here I want it to actually start
7:16again at one
7:17to start counting within office supplies so
7:20what I'll do is I'll go down here I'll go
7:22to compute
7:22using but this time I'll actually choose
7:24the subcategory as the computational thing
7:27that it's
7:27going to use essentially the partition and
7:30when we do that you'll see that it starts
7:32again and so
7:33now I can move the index in front of the
7:35subcategory and it now acts as like a way
7:37of counting
7:37rows this is a really simple example
7:39because you'd probably think well what if
7:42you just go and change
7:43this to category isn't it going to then
7:45start count within within each category the
7:48answer is no
7:49and so to explain that I generally need a
7:51separate video to do that so I'll do that
7:53in another video
7:54you can actually get this to work I'll very
7:56quickly show you why don't pay attention to
7:58why this works if I just go down in here I
8:00'm just going to create another function
8:02called all
8:03I'm going to put this in brackets and I'm
8:05just going to do this very quickly so you
8:08you can see
8:08that it's possible but in order to explain
8:11why it's working I really need more time so
8:13now that
8:13I've created this all function what that
8:15does is it creates another partition for
8:17this category
8:18element over here and you know if I just
8:20removed subcategory and then set the index
8:23to count to
8:24category then it would work but as soon as
8:26I add subcategory into the view you'll
8:29notice that the
8:29count breaks down so how do you fix this
8:31well that's when you bring in the boss
8:33level tools
8:34in the table calculation window and I'm
8:37just going to set this to category and sub
8:39category okay and
8:41the reason I'm doing that is because I'm
8:43sort of creating like a a grouped partition
8:45and then I'm
8:46going to tell it to count at the level of
8:48category and now you'll see that it's
8:51working so what I did
8:52is I selected two items category and sub
8:54category then I told it hey can you work
8:57this table
8:57calculation at the level of the category
9:00and now you see this works and so now I can
9:02exit this and
9:03if I drag it in front of category now I'm
9:05getting it to count the category so in
9:07order to understand
9:09that I need way more time but this is still
9:11the index function doing its thing working
9:13creatively
9:14okay so that's pretty much it for this
9:16partition topic I'm not going to cover any
9:19more of it I'm
9:20going to go ahead and now show you some
9:22more creative uses for the index function
9:24that you
9:24might use in your day-to-day usage of table
9:26au I'm going to open up a new sheet here I'm
9:28just going
9:29to open up a brand new sheet we're going to
9:30start afresh and make it simple because
9:32this thing I
9:33want to show you is actually quite a cool
9:35thing let me bring in subcategory and let
9:37me bring in
9:38cells again we've seen this table already
9:40and let me just type in index okay and you
9:43'll see that
9:44we just use the index function in the ad
9:46hoc calculation here set this to discrete
9:48and now
9:49we're good to go now the thing about this
9:51is what if you want to count backwards okay
9:53let's say that
9:54you actually wanted to assign the highest
9:57number to accessories and the lowest number
9:59to tables one
10:00okay well you can actually do that but you
10:02have to be creative about how you do it you
10:04have to
10:04think of another function that you can use
10:06that will actually do that and that
10:08function is called
10:09the rank function now it's kind of
10:11interesting because the rank function looks
10:13at the largest
10:14number and by default sorts in descending
10:17order so if you wrap the index function in
10:19the rank
10:20function you essentially get a mechanism
10:22for counting backwards so this is one of
10:24those
10:24weird ones where if you just put a function
10:26inside of a function you get a totally
10:28different behavior
10:29that maybe you didn't expect so let me just
10:31type in rank here it'll obviously delete my
10:33index as
10:34I do that and then hit index again and I'm
10:36just going to have to manually type that in
10:38so you can
10:38see here I've wrapped the index function
10:41inside of the rank function I'm going to
10:43hit enter and now
10:45you see that accessories shoots to the top
10:47because it's now the largest item so let me
10:50go and
10:50change this to a discrete item and now you
10:53'll see it's counting backwards okay so this
10:56is a very
10:56creative use of using this let me show you
10:59an actual practical use case that I use all
11:01the time
11:02with dates I'm going to open up a new sheet
11:04I'm going to bring in order date I'm going
11:06to put it
11:06on rows and while it's here I just want to
11:08do a couple of things I want to change the
11:10date type
11:11here to month then I'm going to change this
11:13to discrete we've got that behavior between
11:16you know discrete and continuous items
11:18behaving again that's blue versus green
11:20essentially so
11:21I'm going to bring in the sales value into
11:24the abc section here and now you see this
11:26long list
11:26of items with the most recent data December
11:292020 being at the bottom man 2020 we all
11:32want to forget
11:332020 for lots of good reasons but that is
11:36the most recent data set in this data set
11:38so what I'm going
11:40to do now is I'm going to do this I'm going
11:42to say index okay and you're going to see
11:45the count in
11:46action so now it's counting from top to
11:48bottom and it's doing exactly as expected
11:51except for what if
11:52I just want the most recent roads well if I
11:54was to go to the bottom and just select
11:56number 48 to 46
11:58as more rows get added actually those
12:00numbers will increase so that's not a
12:02reliable way of just
12:03keeping the most recent data yes I could
12:05use a date filter but when you're doing
12:07more complex
12:08filtering it can actually get really really
12:11tricky to define that as a filter so what I
12:14often do is a
12:14sort of little cheat and a hack is I use
12:16the index function to fix this so let me go
12:19ahead in here
12:19and wrap this in a rank let me just type
12:21rank and then I'll just highlight the index
12:24function
12:25and I'll cut it and I'll put it inside of
12:28my rank function and now you'll see what
12:30happens if I go
12:31back make this discrete again you'll see
12:34that now my most recent data has the value
12:37one two three
12:38four five six so if I want the most recent
12:40six months what I then end up doing is I
12:42just hold
12:43shift select one to six keep only and now
12:45as the data updates it's only ever going to
12:48have the most
12:49recent six rows because it's counting from
12:51the bottom rather than from the top and
12:54that's sort
12:55of a really simple but creative use case of
12:57using two functions together rank and index
13:00to sort of
13:01make that work now that's done what you can
13:03also do is you can right click on here and
13:05hide the
13:06header essentially untick this option and
13:08now you don't even have your accounts in
13:10your view so you
13:10can essentially use that to drive some
13:12behavior but then immediately remove it
13:15from the view I
13:15could also just move it here on to detail
13:18and it still works because it's in the
13:19context of my
13:20visualization so you don't have to have it
13:22here in the row shelf for it to do its
13:24thing so that's
13:25just some creative use cases of how this
13:27works and I hope you enjoy that okay so you
13:30can really have
13:31fun with the index function you can really
13:32do a lot of things it looks like a very
13:34simple function
13:34but then it becomes really really powerful
13:37and has lots of use cases and be sure to
13:39check out my
13:40video on the rank function I actually did
13:41that just a few days ago I'll put a link to
13:43it in the
13:44description above and also in the
13:46description below so check that out um I'm
13:48going to stop the
13:49video here I could go on and on about other
13:51use cases for index but I think that's
13:53enough for one
13:54video if you've got other use cases for the
13:56index function drop them in the comments
13:58below I'd love
13:58to know about them let me know on twitter
14:01otherwise you know what to do it's the end
14:03of the video I'll
14:04catch you in the next one if you've liked
14:05the video you know what to do if you don't
14:07let me
14:07know in the comments what you'd like to see
14:09instead and yeah see you in the next video
The index function essentially counts rows in a dataset and it can be controlled we using a partition, a concept I cover in this video along with creative ways of using the index function to achieve interesting filtering results.
- 0:00 Intro
- 0:21The Index () Function
- 4:16 Understanding how it all works
- 9:27 Counting Backwards
- 13:30 Outro