Percentage Growth from Previous Month
With ship months going down the columns, you might want to see the percentage of rev-
enue growth from month to month. You can set up this arrangement with the
xlPercentDifferenceFrom setting. In this case, you must specify that the BaseField is
“Invoice Date” and that the BaseItem is something called “(previous)”:
‘ Set up % change from prior month
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Caption = “%Change”
.Calculation = xlPercentDifferenceFrom
.BaseField = “Invoice Date”
.BaseItem = “(previous)”
.Position = 3
.NumberFormat = “#0.0%”
End With
Note that with positional calculations, you cannot us the AutoShow or AutoSort method.
This is too bad; it would be interesting to sort the customers high to low and to see their
sizes in relation to each other.
Percentage of a Specific Item
You can use the xlPercentDifferenceFrom setting to express revenues as a percentage of the
California market sales:
‘ Show revenue as a percentage of California
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Caption = “% of California”
.Calculation = xlPercentDifferenceFrom
.BaseField = “Market”
.BaseItem = “California”
.Position = 3
.NumberFormat = “#0.0%”
End With
Running Total
Setting up a running total is not intuitive; to do this, you must define a BaseField. In this
example, Invoice Date runs down the column. To define a running total column for revenue,
you must specify that
BaseField is “Invoice Date”:
‘ Set up Running Total
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Caption = “YTD Total”
.Calculation = xlRunningTotal
.Position = 4
.NumberFormat = “#,##0,K”
.BaseField = “Invoice Date”
End With
Chapter 11 Using VBA to Create Pivot Tables
278
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 278
To Next Page
Loading...
Need help?
General
