Tableau Prep Builder: Aggregations (Part 5 of 7)

0:00Okay, welcome back to this video on aggreg

0:04ations in the Tableau prep for Excel users

0:08series.

0:08If you haven't checked the previous videos

0:10out, please do. They are really, really

0:13detailed and

0:13they build up to this video, so I won't be

0:15covering some of the basic concepts. I'll

0:18just

0:18be referring to those videos instead. In

0:20today's video, we're covering the

0:22aggregation step and to

0:24allow you to follow along, I've just opened

0:26up Tableau prep and I'm going to be using

0:28the sample

0:29flow that we've been using all along. Super

0:32store, it's the bottom left flow, and then

0:35when you get

0:36to this particular view, what I want you to

0:39do is delete certain parts of this flow. So

0:42what I'm

0:43going to do is I'm going to ask you to

0:45delete this bottom section of flows and

0:49then all the outputs

0:50here at the top. Okay, so you should just

0:53remain with the clean step in the flow and

0:55nothing else.

0:58Okay, now that you've had the chance to do

1:00that, we're going to get stuck in. Now, the

1:03key thing

1:03to understand here is that if you've used

1:05Tableau Desktop, aggregations have

1:07typically been done in

1:08the calculations window or in the view as

1:11they'd call it, and that's because when you

1:13're visualizing

1:14something, everything is happening inside

1:17of the view. So aggregations and row level

1:19calculations

1:20can all happen in the same place in the

1:22calculation window. However, when you're

1:24working

1:25with raw data at a row level, the aggreg

1:28ations need to be almost described in the

1:31way that you'd

1:32want them to work, because what you're

1:34actually doing is reshaping the data and

1:36rolling up certain

1:37rows together to form the aggregated output

1:40. And so what you'll see here is if I click

1:43on this

1:44plus icon, there's actually a specific step

1:46that's required for that aggregation step.

1:50It's this one

1:50here that I've just highlighted. And if I

1:53click on that, you'll see that Tableau adds

1:55this icon,

1:56which looks like a sigma function. I think

1:58that's a sigma function. If not, let me

1:59know what the right

2:01icon is for that. And you get this sort of

2:04blank pane. Now, unlike previous steps

2:07where we've added

2:08a step, you've always had a summary view

2:11available to you. And the reason you don't

2:13get that here

2:14with the aggregate view is because at this

2:16point, Tableau doesn't know what you want

2:19to aggregate.

2:20And so the way that you describe that is

2:23using these two panes. On the left, we have

2:26the ability

2:26to aggregate by groups. Essentially, these

2:29are the grouping fields. And on the right,

2:31the thing

2:32you're going to be aggregating. So it's a

2:34very simple example. If I want to aggregate

2:37my cells,

2:39and I want that aggregate to be based on

2:42the categories, those are the two things

2:47that I drag in.

2:48And now you start to see the summary

2:50working in full force. Because I've only

2:53brought in the

2:54groups category, I just get one single row

2:58for each category and one single value for

3:01each

3:01category. Now, I can continue to add

3:04different items to this. So for example, I

3:07can add subcategory

3:09to this list on the left. And then again,

3:13we get this grouping. And on the bottom,

3:17you can now see

3:18this broken down. So now I get every

3:20combination of category and subcategory and

3:22its own sales

3:23value. So this is how we aggregate in Table

3:26au. And don't forget, now the summary view

3:28still functions

3:30like we had in the clean step or the just

3:32the normal calculation steps. But as we add

3:36items

3:37to this, different items sort of come in

3:39and come out. Now, you'll also notice here

3:42on the left hand

3:43side that there's a changes column. And so

3:44you might be wondering, well, yes, of

3:46course, I can

3:47see the aggregation step here. And these

3:49are the three fields. What other changes

3:52could I carry out

3:53in this particular step? Well, let's say I

3:55wanted to exclude the furniture cells.

3:57Those changes

3:59would also be captured in this aggregation

4:01step. And the key thing to notice here is

4:04that the

4:04aggregation icon here gets a little bit

4:07smaller to accommodate the icons along the

4:09top. So if you've

4:10done any data cleansing inside of another

4:13step, it's still going to surface those to

4:15you. So again,

4:16Tableau prep is being highly visual about

4:18the changes that you're making. If I go

4:21back to the

4:22settings tab, you'll see that I still have

4:25a list of items here that I have not yet

4:27dragged in. And

4:29so the important thing to bear in mind here

4:31is that for everything that comes from this

4:34step onwards,

4:35we are not going to get any rows that we

4:37don't bring into our aggregation. Okay. If

4:41you wanted to

4:42create a relationship between these aggreg

4:45ated views and the original data, you'd have

4:48to do

4:49like a VLOOKUP or a JOIN to get those two

4:51datasets back together again. So it's just

4:54really important

4:55to understand that the aggregation step

4:57does mean that you are going to lose

4:59certain columns

5:00in the final output from that aggregation.

5:03Now the other thing to bear in mind is that

5:05we are

5:05just aggregating numerical values on the

5:08right hand side, and on the left we're

5:09grouping by

5:10dimensional attributes. But what if I

5:13wanted to aggregate dimensional attributes?

5:15Let's say I

5:16wanted to count the number of regions in

5:19which I have sold a particular product.

5:21Well what I do is

5:23I drag region onto the right hand side.

5:26Okay. And when I do that, notice that Table

5:29au Prep

5:30automatically figures out that this is a

5:32dimensional attribute, and so therefore I

5:34probably

5:34just want to count the dimensional

5:36attributes. So it's going to go in and

5:39count the number of

5:41different regions that it comes across.

5:44Okay. So if I click on that, you'll see

5:46here that that's not

5:47the only aggregation I can do. I can count

5:50the counties distinct, minimum, maximum,

5:53percentile,

5:54base and regions, or I can group by. Group

5:57ing by essentially moves it over to the left

6:00hand side,

6:01so it stops treating it as a sort of

6:04account and it makes it a grouped item.

6:08Again, if I click on

6:09this drop down, I still get the ability to

6:12rename the field, clean it in a particular

6:14way, removing

6:15uppercase, lowercase. All of those steps

6:18will just live here in my changes column.

6:20And so it's really

6:21important to understand that now if I add

6:24an additional cleansing step to this, you

6:27'll see I

6:28now have only the four rows. So it's a

6:31really important step to understand, but it

6:33's also a very

6:34very powerful step because it allows us to

6:36do some really really powerful things, like

6:39we can

6:39calculate totals for a category or a

6:42department and then join that or vlookup

6:45that back to a

6:46data set so that we can have that available

6:48to us as a calculation. Let's say I want to

6:51know what

6:51percentage of cells did Central contribute

6:55to the total cells of the entire country.

6:58Well, in order

6:58to do that, I'd need to know the total

7:00cells for the entire country and that

7:02requires an aggregation

7:04in my data set. At a very high level, that

7:07's pretty much it with the aggregation steps

7:09. There are lots

7:10of different aggregations you can look at,

7:12there are lots of different ways you can

7:14work with it,

7:14but essentially if you take this sort of

7:17aggregation, this is your full list of

7:19different

7:20types of aggregations that you can do. They

7:22're very similar to what you can do in Excel

7:24, so if

7:24you're familiar with Excel, you're already

7:26familiar with many of these aggregations.

7:28And last but not

7:30least, just remember that when you group or

7:32bring an item in, if you forget to bring

7:34anything in,

7:35those aggregations will not come through.

7:38So if I needed, for example, this

7:41aggregation at a

7:42product level and I don't bring that in,

7:44the aggregation will not be set to the

7:46correct level.

7:47Okay, that's it, that's it. It's a very

7:50very simple feature set. If you've enjoyed

7:53this video,

7:54hit subscribe. If not, drop a comment below

7:56, let me know what kind of content you'd

7:57like to see,

7:58and I'll catch you in the next video where

8:00we'll look at transformations in beta.