 |
|
Pivot table tutorial
Basic components of a Pivot Table
As seen in the pivot table example below there are 3 main parts to a Pivot table.
- Rows
- Columns
- Facts (also known as measures or data fields)
Figure 9
The blue triangle indicator next to Shirt Style means that a custom sort was applied
to this dimension. This is not standard to Pivot tables. It is numberGo specific.
The dimension was sorted to match the spreadsheet. The default sort is ascending.
The Facts are the numerical dimensions. In the case of our sample data, these are
Units, Price and Cost.
The Columns and Rows can be any dimension type. The Row dimensions create a Row
for every value and the Column dimension creates a column for every combination
of Column fields and Facts. There can be as many columns as you want - more on columns
later.
Looks like a spreadsheet; what’s the difference other than the Ship Date being
a column?
Now we’ll get into what makes a Pivot table a Pivot table.
All the pivot table examples thus far have been really simple and mimic a spreadsheet so you
can understand the basics.
This next image shows a total for each Region and Category along with more dates.
Figure 10
Notice the yellowish rows. These are the totals for East and North and Boys and
Girls. The Pivot table automatically sums the rows. The numberGo Publisher provides
many more aggregation types such as average, max, min, count, weighted average,
error and more. A single dimension can have more than one total line. In many cases
you might want to see the Sum and Average.
Also notice that South and West have a + sign to their left. This means the rows
were collapsed. You’ll see the single row for South and West displays the sum for
each Fact for each Ship Date.
If I remove Category, there are fewer rows because all the rows for each Region
are aggregated to the Shirt Style.
Figure 11
Looking at figure 10 you’ll see that there were 11 Boys Tee shirts sold on 1/1/2005
and 14 Girls Tee shirts sold on 1/1/2005 in the East. In figure 11 Tee shows a totals
or 25 sold.
Delving in deeper with numberGo Date Group fields
Even though Date Group fields are not standard to a Pivot table, it’s worth showing
because it gives a better understanding of why a Pivot table is so valuable.
numberGo Publisher has the ability to create Date Group fields (or dimensions) from
a single basis field.
Take the Ship Date field. You’ll see values such as 1/1/2005, 2/1/2005, 3/1/2005,
etc… There are monthly dates for every month from 1/1/2005 – 12/1/2007. By taking
this date and slicing it into different parts, numberGo Publisher allows you to
create Year, Quarter, Month and Week values along with others down to the second.
Why is this important? Many times you want to see your data rolled up to the year
or quarter. In a spreadsheet this would require a lot of work but in Pivot Table
(providing you have to proper dimensions), it’s just a click away.
Figure 12
Notice in figure 12 that we now have ShipDate Year, ShipDate Quarter and ShipDate
Month. These new fields allow us to easily rollup our numbers they way we want.
As seen in figure 12, we get the totals for 2005, the totals for Q1 in 2006 and
monthly for Q2 in 2006.
|
|
|
|
|