[Previous] [Contents] [Next]

Summarize Date Fields with Grouping

With transactional data, you will 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.

Creating a group with VBA is a bit quirky. The .Group method can be applied to only a single cell in the pivot table, and that cell must contain a date or the Date field label. This is the first example in this tutorial where you must allow VBA to calculate an intermediate pivot table result.

You must define a pivot table with In Balance Date in the row field. Turn off ManualCalculation to allow the Date field to be drawn. You can then use the LabelRange property to locate the date label and group from there. Figure 16 shows the result of Listing 4.

Listing 4. Code That Uses the Group Feature to Roll Daily Dates Up to Monthly Dates

Sub ReportByMonth()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PivotTable")
    Dim WSR As Worksheet

    ' 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(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row fields
    PT.AddFields RowFields:="In Balance Date", ColumnFields:="Region"

    ' Set up the data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0"
    End With

    ' Ensure that you get zeroes 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

    ' Group ShipDate by Month, Quarter, Year
    PT.PivotFields("In Balance Date").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

End Sub

16. The In Balance Date field is now composed of three fields in the pivot table, representing year, quarter, and month.

[Previous] [Contents] [Next]