Grouping by Week

You probably noticed that Excel allows you to group by day, month, quarter, year. There is no standard grouping for week. You can define a group that bunches up groups of 7 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 1, 2004 through Wednesday January 7, 2004. 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. It is not valid to group by week and quarter.

The following code creates the report shown in Figure 12.32:

Sub ReportByWeek()

Dim WSD As Worksheet Dim PTCache As PivotCache

Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long

Set WSD = Worksheets("Pivot Table")

' Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"),

TableName:="PivotTable1") PT.ManualUpdate = True ' Set up the row fields

PT.AddFields RowFields:="ShipDate", ColumnFields:="Region"

With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" .Name = "Total Revenue" End With

' Ensure that we get zeroes instead of blanks in the data area PT.NullString = "0"

' Calc the pivot table to allow the ShipDate label to be drawn PT.ManualUpdate = False PT.ManualUpdate = True

' Group ShipDate by Week.

'Figure out the first Monday before the minimum date FirstDate = PT.PivotFields("ShipDate").LabelRange.Offset(1, 0).Value WhichDay = Application.WorksheetFunction.Weekday(FirstDate, 3) StartDate = FirstDate - WhichDay PT.PivotFields("ShipDate").LabelRange.Group _ Start:=StartDate, End:=True, By:=7, _

Periods:=Array(False, False, False, True, False, False, False)

0 0

Post a comment