Microsoft Office Tutorials and References
In Depth Information
Figure 20-33. The finished calculation
Now, the trick in seeing how the calculations are made is to fix a value for the fields other than the
base field—in our case the Store Type and Period. Consider, for instance, the values:
Store Type = "Company"
Period = 1
The Sale data cells corresponding to these values are grayed in Figure 20-32 . One of these cells
(cell D4) is the base item cell. For this Store Type/Period combination, a calculation is made using
the value in this cell as the base value. In our case, it is the xlDifferenceFrom calculation that
is being made. Hence, the base value is subtracted from the values in all three grayed cells. This
gives the table in Figure 20-33 . Note that the base value is even subtracted from itself, giving 0.
This is done for each Store Type/Period combination, as shown in Figure 20-33 .
Figure 20-33. The finished calculation
The formulas for the Calculation property that require BaseField/BaseItem values are:
xlDifferenceFrom
# - base value
xlPercentOf
#/base value (expressed as a percent)
xlPercentDifferenceFrom
(# - base value)/base value (expressed as a percent)
To illustrate, Figure 20-34 shows the actual effect of the earlier code on Figure 20-10 :
With ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Sale")
.Calculation = xlDifferenceFrom
.BaseField = "Store City"
.BaseItem = "Boston"
End With Figure 20-10 .
Figure 20-34. Illustrating the Calculation property
 
 
 
Search JabSto ::




Custom Search