Microsoft Office Tutorials and References
In Depth Information
Working with Dates
Up to now, you have used columns that contain a relatively small set of distinct values, such
as the color or the category of products, to slice data in the PivotTable. When, on the other
hand, a column contains a lot of distinct values, the resulting PivotTable gets harder to use.
We are now going to describe this problem in greater detail and provide a solution for it.
The SalesOrderHeader table contains a column, OrderDate, which records the date of the
order. The details in this column are important but, for the purpose of reporting, the column
contains too much information. If you simply put the OrderDate data in columns, you end
up with a report that contains all the information you need but is very difficult to read (see
Figure 2-29) because of the high fragmentation of values. In technical terms, we say that the
order date column is not a good aggregator because it does not let us focus on interesting
information. A good aggregator, on the other hand, groups together a huge number of
distinct elements of information, leading to interesting results. You can find this example
in the workbook CH02-05-WorkingWithDates.xlsx.
FIguRE 2-29 Date columns are not good aggregators; the report is sparse.
As you can see, browsing information at the date level produces a sparse report. A much
better aggregator would be the year or the month level. Both of those aggregators greatly
reduce the fragmentation of the report and result in a better understanding of the data.
PowerPivot can aggregate data, but to do that, it needs columns. So you need to add new
columns to the SalesOrderHeader table that contains the year and to the table that contains
the month of the order. Aggregating for these columns produces the result you want.
Note As you can see, we are shifting our concern from the problem of the sparse report to that
of adding new calculated columns, and because we already know how to add new calculated
columns, we are now finding our way to the solution of our problem.
You can add two new calculated columns to the SalesOrderHeader table in the PowerPivot
window, following the already described procedure, and use Table 2-2 to get the formulas.