265
Summarizing Date Fields with Grouping
‘ Set up the row fields
PT.AddFields RowFields:=”InvoiceDate”, ColumnFields:=”Region”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
End With
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
‘ Group ShipDate by Month, Quarter, Year
PT.PivotFields(“InvoiceDate”).LabelRange.Group Start:=True, _
End:=True, Periods:= _
Array(False, False, False, False, True, True, True)
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
WSD.Activate
Range(“R1”).Select
End Sub
Group by Week
You probably noticed that Excel allows you to group by day, month, quarter, and year.
There is no standard grouping for week. You can, however, define a group that bunches
groups of seven days.
By default, Excel starts the week based on the first date found in the data. This means that
the default week would run from Thursday, January 5, 2006, through Wednesday,
December 31, 2008. You can override this by changing the
Start parameter from True to an
actual date. Use the
WeekDay function to determine how many days to adjust the start date.
There is one limitation to grouping by week. When you group by week, you cannot also
group by any other measure. Grouping by week and quarter is not valid.
Listing 11.7 creates the report shown in Figure 11.18.
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 265
To Next Page
Loading...
Need help?
General
