Data Field Calculation

Normally, data fields show a value based on the Function property of the field. On the other hand, Excel also permits us to change the meaning of the value that is displayed in a data field.

20.7.8.1 Calculation property

This is done by setting the Calculation property for the data field. (The property applies only to data fields.) The possible values of this property are given by the following enum:

Enum XlPivotFieldCalculation xlNoAdditionalCalculation = -4143 xlDifferenceFrom = 2 xlPercentOf = 3 xlPercentDifferenceFrom = 4 xlRunningTotal = 5 xlPercentOfRow = 6 xlPercentOfColumn = 7 xlPercentOfTotal = 8 xlIndex = 9 End Enum

As you can see from these constants, the idea is to replace the raw value in the data field by a type of relative value. (We will see an example in a moment.)

Note that for some values of Calculation, additional information is required. In particular, if Calculation is equal to xlDifferenceFrom, xlPercentDifferenceFrom , or xlPercentOf, then we must specify the following two properties:

BaseField property

This property, which applies only to data fields, returns or sets the field upon which the data field calculation is based.

Baseltem property

This property, which applies only to data fields, returns or sets the item in the base data field used for the calculation.

20.7.8.2 Calculations not requiring a BaseField/Baseltem

The Calculation values that do not require a BaseField/Baseltem pair are: xlRunningTotal

Keeps a running total of all values, going down the rows of the table.

xlPercentOfRow

Each cell is replaced by that cell's percentage of the sum of the values in that cell's row (for the given data field).

xlPercentOfColumn

Each cell is replaced by that cell's percentage of the sum of the values in that cell's column (for the given data field).

xlPercentOfTotal

Each cell is replaced by that cell's percentage of the sum of the values in the entire table (for the given data field).

The formula to compute the value in a cell is:

new value = (current value * grand total) / (row total * column total)

Figures 20-28 through 20-31 illustrate these calculations, starting with the pivot table in Figure 20-10. Note that the percent calculations require that the grand row and column totals be displayed. The code for Figure 20-28 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlRunningTotal

Referring to Figure 20-28, cell D6 contains sales for Boston, cell D10 contains total sales for Boston and Los Angeles, and cell D14 contains total sales for Boston, Los Angeles, and New York. (I have had reports that the Calculation property does not always work properly. For some reason, it may simply cause the relevant cells to fill with Excel's infamous #N/A symbols. Indeed, I have had this same experience at times, but I have not been able to figure out why.)

Figure 20-28. Calculation = xlRunningTotal

3

Period 1

4

Store

Store Type

Data

1

2

3

4

5

BO SI

Company

Trans

28248

2 ¿714

23672

23602

6

Sato

44673

46256

46223

7

Franchise

Trans

13993

13942

13275

132 10

S

Sale

21816

21739

13632

1S325

9

LOS

Company

Trans

34538

35938

35692

35001

10

Sale

168156

17S491

172596

171641

11

Franchise

Trans

7 1533

72947

75619

74392

12

Sale

2342<17

290013

297957

292356

13

NEW

Company

Trans

24616

25104

27015

26354

14

Sate

24624? 251742

'A; -U'

259699

15

F ranch ise

Trans

53273

54351

51 £22

51218

16

Sale

460600

4724-74

463494

460302

The code for Figure 20-29 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfRow

Figure 20-29. Calculation = xlPercentOfRow

A

B

C

D

E

F G

H

1

Year

(A: ^

2

Period

4

ttone C ity

SL.ore Type

Data

1

2

3 4

Grand Total

5

eOSTt )N

rran&hise

Tr-^nc-

13903

13943

13275 13210

5<H?0

6

Sale

27 10%

27 00%

23 14% 22 70%

100.00%

7

Company

Trans

282-iS

2871J

23672 2S602

114230

8

sate

24 27%

25 49%

25 13% 25 11%

100 00%

9

LOS ANGELE;

Company

Trans

34538

35938

35092 35001

141219

10

Sate

48%

25 67%

25 03% 24 85%

100 00%

11

Franchise-

Trans

"15&3

72947

75619 74332

294541

12

Säte

20%

24.74%

25 25 31%

inn 00%

13

NEW YORK

Company

Trans

>1616

36104

27015 ?68>1

10-1589

14

Sale

2? 0J %

25.13%

25 9 1% 2'i: 95%

100 00%

15

Franchise

Trans

53273

54351

51822 51218

210004

16

Sale

2531%

26 19%

24 4"% 24 03%

100 00%

.1 -r

The code for Figure 20-30 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfColumn

Figure 20-30. Calculation = xlPercentOfColumn

A

B

C

D

E

F

G

H

I

Year

(AN)__.7.

2

3

Period

4

Store

Stone Type

Data

1

2

4

Grand Total

5

BOS"

Company

Trans

23248

28714

28872

23502

114236

6

Sale

i? 32%

6 39%

6 35%

3 42%

6 37%

7

Franchise

Trans

13993

13942

13275

13210

54420

8

Sals

3 09%

2.96%

2 56%

2 55%

2 79%

9

I OS

rompany

Tran--,

345,33

3593ft

3569"

35TJÜ1

141219

to

Sa^e

17 47%

17.85%

17 33%

17 42%

17.47%

11

Franchise

Trans

7 l$S3

72947

IS

74^92

29454 I

12

Sale

37 13%

38 32%

■■ |

37 53%

13

NEW

Company

Tians

24616

281Ü4

27015

2S&54

104589

14

Sals

11 05%

116 1%

1 ? ,":6%

1/ ;3%

11 74%

15

Fra-xhisi

Trans

53273

5435 1

51822

51213

210804

16

24 95%

24.85%

23 39%

2 S 26%

24 11%

17

Torsi T-ans

226301

231996

232095

229277

919869

16

Total Sale

'00.00%

1 00.00%

100 00%

103C0%

10000%

in

The code for Figure 20-31 is:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Sale").Calculation = xlPercentOfTotal

Figure 20-31. Calculation = xlPercentOfTotal

ADCDCTG H

Period

Store

Store Type

Data

12 3 4

Grand Total

BOS1

Company

Trans Sale

2324$ 28714 28672 28602 1 55% 1 62% 1 60% 1 60%

114236 6 37%

Franchise

Trans Sale

13993 13942 13275 13210 0 75% 0.75% 0.64% 0.63%

54420 2.79%

LOS

Company

Trans

34538 35933 35692 35001 4 27% 4.48% 4 37% 4.34%

14:219 17.47%

Franchise

Trans Sale

71583 72947 75619 74392 9 03% 9.28% 0.67% 9.50%

¿94541 37.53%

NEW

Company

Trans Sgte

24616 26104 27015 26 £¡54 ? 70% ? QS% 3 04% 3 05%

S04539 11 74%

Franchise

Trans Sale

53273 54351 5I622 51213 6.10% 0.31% 5.90% 5.79%

2 10664 24.1 f%

Tctal i nans

226301 23190S 232095 229277

919669

Total Sale

24 40% 25.40% 25.22% 24.91%

10000%

20.7.8.3 Calculations requiring a BaseField/BaseItem

The procedure for making calculations with a BaseField/BaseItem is not explained very well in the documentation, so let us see if we can clear it up by first considering an example. Consider the code:

With ActiveSheet.PivotTables("Sales&Trans"). PivotFields("Sale") .Calculation = xlDifferenceFrom .BaseField = "Store City" .BaseItem = "Boston" End With

Referring to Figure 20-32, we have reproduced our usual pivot table (from Figure 20-10) with several changes. First, we removed the Trans values, since they are not relevant to our example. We have also replaced the Sale values for the given BaseField and BaseItem by symbols (b1-b8). Finally, we replaced the other Sale values with number signs, since we do not care about the actual values.

Figure 20-32. Illustrating a calculation

A

B

C

D

E

F

G

1

Period

2

Store City

Store Typi

Data

1

2

3 4

3

BCSTON

Company

'I rani

4

Stile

bl

b2

b3

b4

5

Franchise

Trans

Sale

b5

bG

b7

be

7

LOS ANGI

Company

Trans

3

Sale

#

k

k

I*

9

Franc hise

Trans

10

Sale

#

k

k

11

NEW YOR

Company

Trans

'2

Sate

#

k

'3

Franchise

Trans

14

Sale

#

k

k

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

A

B

C

D

E

F

G

1

Period

Store City

Store Typ(

Data

1

2

3 4

BOSTON

Company

Trans

4

Sale

0

0

0

0

5

FraiTçh se

Tr$ii$

Säle

0

0

0

0

7

LOS ANGI

Company

Trans

0

Sala

s-bi

if-b?

S

rranch se

Trans

10

Sale

£b5

*-b6

^■b7

itf-b8

11

NEW VOR

Corrçgny

Trans

12

S sie

£-b4

13

branch se

Trans

14

Sale

#-b5

s^be

#-h7

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" .Baseltem = "Boston" End With Figure 20-10.

Figure 20-34. Illustrating the Calculation property

A

B C

D E F G

1

Year

(AH) h[

2 3

Period |

Slore

Store Type

Data

12 3 4

6 7

BO SI

Company

Trans Sate

28248 28714 23672 23602

Franchise

Trans Sale

13993 13942 13275 13210

10 11

12

LOS

Company

Trans Sale

34533 35938 35692 35001 78800 32637 30084 79195

Franchise

Trans Sale

71583 72947 75619 74302 240615 2<1 £535 260693 256206

16

NEW

Company

Trans Sale

24616 25104 27015 26354 33411 38324 4164^ 41835

Franchi se

Trans Sale

53273 54351 51822 51218 154537 160722 151905 149121

0 0

Post a comment