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.
Tableau | Data prep