Measuring Order Lead Time by Grouping Two Date Fields

Recall in the last section how Excel adds field names when you group by month and year. The less-aggregated measure (month) inherits the name of the original field. Any more-aggregated measures receive the name of the grouping—for example, "Years."

Your manufacturing plant may be concerned with a measure of how far in advance the orders are received. If the plant has a 12-week lead time to procure components, they would love to have all orders placed 13 weeks in advance. When this doesn't happen, it is critical that you have an excellent forecasting system in place to accurately predict orders.

If you can add an OrderDate field to your transactional data, you could build a table to show how much revenue is received x months in advance of the ship date.

Follow these steps to set up an interesting potential anomaly:

1. Build a pivot table with ShipDate down the side. Group ShipDate by month and year. This creates fields called ShipDate and Year.

2. Move these fields to the column field section of the pivot table.

3. Add OrdDate to the row field of the pivot table.

4. Group OrdDate by month and year. This creates a field called OrdDate with data by month. The grouping of OrdDate by year would tend to also be called "Years," but Excel instead calls it "Year2."

Newer versions of Excel correctly deal with the second set of grouped date fields by changing the field name to Year2 instead of having a second Year.

The following code produces the report shown in Figure 12.33:

Sub MeasureLeadtime()

Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range

Set WSD = Worksheets("OrderDate")

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

PT.TableRange2.Clear Next PT

1 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("K2"),

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

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

1 Group ShipDate by month and year

PT.PivotFields("ShipDate").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True)

1 Now that we have less than 365 data points, move to column field With PT.PivotFields("Years")

.Orientation = xlColumnField .Position = 1 End With

With PT.PivotFields("ShipDate") .Orientation = xlColumnField .Position = 2 End With

' Add Order Date as a row field With PT.PivotFields("OrdDate") .Orientation = xlRowField .Position = 1 End With

0 0

Post a comment