263
Summarizing Date Fields with Grouping
11
Figure 11.16
After the components
that make up the calcu-
lated Plants Group item
are hidden,the total rev-
enue for the company is
again correct.However,it
would be easier to add a
new field to the original
data with a
Responsibility field.
Look closely at the results shown in Figure 11.15. The calculation for Plants Group is cor-
rect. The approximate $2.3 million for the Plants Group is the sum of $1.1 million of land-
scaping and $1.2 million of Green Plants. However, the grand total should be about $9.2
million. Instead, Excel gives you a grand total of $11.6 million. The total revenue for the
company just increased by $2.3 million. Excel gives the wrong grand total when a field con-
tains both regular and calculated items. The only plausible method for dealing with this sit-
uation is to attempt to hide the products that make up the Plants Group:
With PT.PivotFields(“Product”)
.PivotItems(“Landscaping/Grounds Care”).Visible = False
.PivotItems(“Green Plants and Foliage Care”).Visible = False
End With
The results are shown in Figure 11.16.
Figure 11.15
Unless you love restating
numbers to the
Securities and Exchange
Commission,avoid using
calculated items.
Summarizing Date Fields with Grouping
With transactional data, you often find your date-based summaries having one row per day.
Although daily data might be useful to a plant manager, many people in the company want
to see totals by month or quarter and year.
The great news is that Excel handles the summarization of dates in a pivot table with ease.
For anyone who has ever had to use the arcane formula
=A2+1-Day(A2) to change daily dates
into monthly dates, you will appreciate the ease with which you can group transactional
data into months or quarters.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 263
To Next Page
Loading...
Need help?
General
