0:00Hey Tim here, in 21.4 Tableau have added t
0:02iling to Tableau Prep. Now if you're not
0:04familiar with
0:05tiles this is something that's been used in
0:07Tableau, sorry Tableau, all tricks for a
0:10while
0:10and essentially tiling allows you to create
0:13groups of a specified size equally across
0:15your whole
0:16data set. So what I'm going to do is show
0:18you how to use this inside of Tableau Prep.
0:20It's not a new
0:21type of you know object here in Tableau
0:23Prep, it's not one of these new items here
0:27in this sort of
0:27drop down list, it's just a function that
0:29you can use inside of a calculation. So let
0:32's find out how
0:32to use it. Here in this flow I've been just
0:34using a standard flow, I'd actually just
0:36use this to show
0:37the parameter capability in Tableau Prep. I
0:39actually apply the parameter right here, so
0:42what
0:42I'm going to do is add this new function
0:44right before that step because I want to
0:46keep this
0:46context in my whole entire data set. So I
0:49'll go ahead and hit a plus here and just
0:51add a clean
0:52step and you can see that Tableau Prep
0:54makes space in my flow for me. Now to use
0:57this new function
0:58you do need to create a calculation, so let
1:00's go ahead and create a calculation and the
1:02thing you
1:03want to do here is just go to the
1:04analytical functions in here and you can
1:07see that there's
1:08the nTile function, this is what we're
1:10actually going to be using. Now I have to
1:13say the nTile
1:13function uses a group of functions I'm not
1:15really fond of, I think I find them really
1:18cumbersome to
1:18use. The partitioning and order by
1:21functions in this particular version are
1:24fine, they do
1:25exactly what they say on the tin and in
1:26fact in previous versions they didn't do
1:28what they said
1:29on the tin, there's a bit of a bug there
1:30for a while but nonetheless they do work
1:32now and now
1:33what you can do is you can essentially put
1:36the nTile function inside of this partition
1:39and order
1:39by setup here to essentially specify the
1:42number of groups you'd like once you've
1:44ordered your data
1:46using a particular partition. So what the
1:48partition does is essentially defines your
1:50groups,
1:51your order by essentially decides the rank
1:53of those particular things and then the
1:56ascending
1:57or descending decides the direction whether
1:58you're going from the bottom to the top or
2:00from the top
2:01to the bottom and the nTile is just
2:03essentially then taking that final list and
2:05dividing it into
2:06the equal number of whatever you specify,
2:09so here you'll see it says three and once
2:11it's divided it
2:12into three groups it gives them a number
2:14one two three if I say 10 it will say one
2:16two three four
2:17five six seven eight nine ten. So you sort
2:19of hopefully get the idea here, so what we
2:20're going
2:20to do to keep this super simple, so I'm
2:23just going to make sure that I use this
2:26function that you can
2:27see on screen so you've got a reference
2:28point and I can actually walk you through
2:30it. So let's go
2:31ahead and start typing, let's open up those
2:33curly brackets, let's see if we can make
2:35this larger,
2:35I can make the whole thing larger, maybe I
2:38want to just, I want to see if I can just
2:40make the
2:41calculation window larger, I can't seem to
2:43do that, so that's annoying, so what I'll
2:45do is I'll just
2:46I'll just have to sort of take the hit and
2:48make the whole thing larger, so that's fine
2:50.
2:50Let's start, so let's actually don't need
2:53to type the curly brackets, if you just
2:55type in partition
2:56tablet autocomplete for you, I prefer this
2:58because then I can actually structure the
3:00calculation in
3:00the correct way without making a mistake.
3:03So let's go back to the partition, I'll do
3:05this on a
3:05separate line, I don't like to do it as
3:08Tableau suggested, so I'll just type in
3:10customer name in
3:11this particular case and then at the end of
3:13this I'll actually put a colon because this
3:15is what we
3:16need to do to go to the next step of our
3:17function and the next step of our function
3:19we then need to
3:20use the order by function, so let's just
3:22bring that in order by, you can see it's
3:24right at the bottom,
3:25Tableau's calling out that we want to use
3:27that. Again I'll do exactly the same thing,
3:30I'll open
3:31up some space for it and for the order by
3:33function what we need to do is we need to
3:35tell it what we
3:35are going to order with, so we are going to
3:38order our sales, so this is our sales value
3:40, we are going
3:41to use that and we are going to order this,
3:43let's say descending, let's just mix things
3:45up, so it
3:46says ASC here, so DESC is the abbreviation
3:48for descending, it does come up there in
3:51autocomplete
3:52if you are not sure and so once we've done
3:54that we are actually going to define the
3:56number of tiles,
3:57essentially the number of groups to group
3:59these things by, so if I just type in end
4:01tile, you'll
4:02see that it comes up and then I can define
4:04the number here and in this one I think I'm
4:06going to
4:06use 5, actually not 15, 5 as a number, so
4:09you can see my calculation is indeed valid,
4:12let me just
4:13sort of break this down for you once again
4:15so you're absolutely clear what's going on.
4:17The first
4:17thing we're doing is we're defining our
4:19grouping, in this particular case we're
4:21defining our grouping
4:22using customer name, so what Tableau will
4:24do is we'll go find all our customers and
4:26essentially
4:26just save it as a list, once it's done that
4:29it's going to then order that the customer
4:32list by
4:32sales in descending order, that descending
4:35order is going to then define the direction
4:38of the table
4:39and it'll essentially put the customers in
4:41a specific alignment essentially, once that
4:44is
4:44done it will then run the entire function
4:46here, the entire function being the
4:48grouping function
4:49and essentially will create five equal
4:51groups in that data set, don't forget it's
4:54doing this on the
4:55customer name, so it's not doing it based
4:58on number of rows it's just doing it based
5:00on the
5:00customer name, so you've got to imagine in
5:02your head that we have one row per customer
5:05and then
5:05it's creating the groups, that's important
5:07for the next bit I'm about to show you, so
5:09now that it's
5:10done that we're pretty much good to go, let
5:11's clear the annotations and let's call this
5:13customer
5:14grouping and so our top 20% customers in
5:20this sort of file equal spreading of the
5:24customer names
5:25should all get the tile number one and so
5:27on and so forth and the worst ones should
5:29get tile number
5:30five, so this could be like a way of
5:33grouping customers if you wanted to, so let
5:35's hit save
5:36and you'll see that we get the new column
5:39over here, now the reason I said before
5:41that it applies
5:42it to the customer name not on a row by row
5:44basis is because you would think that if
5:46you spread out
5:47your customers in equal sort of way into
5:50five equal groups you'd think you'd get the
5:53same
5:53number of rows in each and you actually don
5:55't because of course the bigger spending
5:57customers
5:58tend to occupy more rows in your data and
6:00so you actually see here they represent a
6:02slightly bigger
6:03chunk than your smallest sort of group of
6:05customers, so this got me thinking
6:07initially
6:08in there I was looking at this saying this
6:10should all be equal but then I realized no
6:12it all depends
6:12on the grain or the level of granularity of
6:15your data so you need to understand that,
6:17check out my
6:17video on that for Tableau Desktop but
6:19nonetheless you can see that this is all
6:22there and if I click
6:23on one you can see it represents 22, the
6:25other one is 20, 20, 20 and then this last
6:29one is 18,
6:30so these got a fairly even spread it's
6:32almost like the binomial distribution where
6:34everyone in the
6:35middle is roughly the same and there's a
6:37tail end of people with tiny transaction
6:39and there's a sort
6:40of massive bump of people with most of the
6:42transaction value there, we can actually
6:45add an
6:45aggregate step just to sort of look at this
6:47in a bit more detail so let's go ahead and
6:50grab some
6:50values so the values that have been created
6:52here are going to be, where has it gone
6:54here,
6:55where did it go, disappeared on me, so
6:58delivery mode, dispatch, customer grouping,
7:01I think this
7:02is one, here we go, so we can actually
7:04bring the customer grouping fields, no that
7:06's country and
7:07region, I did I selected the wrong thing,
7:09let's try this again, customer grouping, we
7:11'll put that
7:11in here you'll see one, two, three, four,
7:14five, then we'll grab the sales values for
7:16these and
7:16we'll see that the grouping number one here
7:19has 20 million worth of sales, grouping
7:21number two,
7:22the next biggest group has five million,
7:24grouping number three has two million,
7:26grouping number four
7:27one million and grouping number five even
7:30less, it's sort of this nice stepped sort
7:32of grouping
7:33and you can sort of look at those
7:35transactions as a group over in tableau or
7:37something else,
7:38of course if I was to then sort of just add
7:41another clean step here and go back to the
7:43summary view you'd see this, let's just
7:46make this a summary view by going to the
7:48view state,
7:48selecting summary and you can actually see
7:50here that you do get five equal rows
7:52because of course
7:52there's only one line per row, I don't know
7:54why I went to this view but there's
7:55actually a lot more
7:56sort of granularity in the data there, I
7:58should have gone to another view and sort
8:00of just limited
8:01the number of fields but nonetheless you
8:03can see the tiling function working as
8:05expected,
8:05so this is going to be a nice quality of
8:07life improvement if you do need to group up
8:09your data
8:09in a specific way, it does need to be used
8:12in conjunction with the partition and the
8:14order by
8:15functions, these are sort of fairly
8:17straightforward functions to understand,
8:19what I'd say to you is
8:20really try and use them and see how they
8:23work, don't try and sort of confidently go
8:25where you
8:26haven't been before without really
8:27understanding what's going on, first use
8:29the partition then use
8:31the order by and really sort of test what
8:33your understanding is expecting versus what
8:36you see
8:36and once you do that you'll notice that you
8:38know level of granularity really does
8:40matter and also
8:41you'll start to see how you can use this in
8:43lots of interesting ways, the partition and
8:45order by
8:46functions in here are really really good,
8:47you could also use rank in this particular
8:49case
8:50rather than order by that's another way of
8:52working with it, so if I just replace this
8:53with rank
8:54and you'll see that this function still
8:57works, if I use that then you'll see that,
9:01actually the function doesn't work because
9:03let me just, actually let me check this, so
9:07I thought the function would work, so if I
9:09go to analytical we have rank, we have
9:11order by,
9:12and so that one is different I guess
9:15because you see this is why I hate this
9:19group of functions
9:20because I always forget, it doesn't matter
9:23how much I try, I always forget how exactly
9:26I'm
9:27supposed to use them because you would
9:29think that rank and order by would work in
9:31the sort of same
9:32way but you can see rank order by here can
9:34actually include a rank so that that makes
9:37no
9:38sense to me so you can even see here I've
9:40been confused by this, I went into a
9:42perfectly sort of
9:43fine calculation and I've made it worse
9:46actually by trying to use something that
9:49you can't use,
9:50so I'll leave it as is where it's working
9:51and we'll call the video there, thanks for
9:54watching
9:54I'll catch you in the next video.
Tableau | Data prep | Analytics