 |
|
Pivot table tutorial
So what is a dimension and how does it work?
In this pivot table example, let’s
take the first column; Region. In this column you’ll see there are only 4 different
values, East, West, North and South. The Pivot table can take all these rows (25)
and condense it down to just the 4 values.
Figure 2
It does this by matching up values and putting them into a group. For example the
6 rows for East have all been consolidated into a single value.
The same is done for Category and Shirt Style
Figure 3
 |
Figure 4
 |
Since this is still confusing, I’ll show you what it looks like in a Pivot Table.
Figure 5
The dimensions in this pivot table example (these were the columns in the spreadsheet) are in the same order
as in our sample spreadsheet.
The first thing you probably noticed is that each Region value only appears once
and that the Category gets repeated for each Region value and Shirt Style is repeated
for each Category. The data is grouped based on the order of the fields. For example,
if I change the order of the fields, you’ll see that the values are either consolidated
or expanded based on their order.
Figure 6
Now each Category value appears once and the Shirt Style is repeated for each Category
and Region is repeated for each combination of Category and Shirt Style. The fields
are refereed to as a dimension because in a Pivot table they add depth to your data.
Figure 7
As you can see in this side by side, a Pivot table presents the data in a much cleaner
and organized fashion.
You should now have an understanding of what a dimension is and how it works.
What about Ship Date?
I left Ship Date out of the previous pivot table example because it requires a further discussion.
Even through the Ship Date is a dimension like Region, Category and Shirt Style
it is typically used to aggregate data over time.
Remember the original spreadsheet
above had 25 rows and 7 columns? Well the full spreadsheet contains 865 rows and
7 columns. Region, Category and Shirt Style are repeated for each date. The different
dates make up the additional rows.
First we’ll add Ship Date to the right of Category
in the Pivot table.
Figure 8
The red square next to Ship Date indicates that all the other dates are filtered
out except 1/1/2005. This indicator is not standard to Pivot tables. It is numberGo
specific.
The Pivot table is now starting to look a little more like the original spreadsheet
minus the Units, Price, and Cost columns.
To demonstrate how the pivoting really works, I’ll need to explain more about the
layout of a Pivot table.
|
|
|
|
|