Calculated Items and Calculated Fields

We have seen that it is possible to add a calculated field to a pivot table. A calculated field is special type of PivotField object that is not part of the original source data, but, instead, is calculated from source fields using a formula. Note that there is no such thing as a CalculatedField object, but there is a CalculatedFields collection.

As we have seen, to create a new calculated field, we use the Add method of the CalculatedFields collection of the PivotTable object. The syntax is:

CalculatedFieldsObject .Add(Name, Formula)

where Name is the name of the field and Formula is the formula for the field.

On the other hand, a calculated item is a special type of Pivotltem object associated with a given PivotField object. (There is no such thing as a Calculatedltem object, but there is a Calculatedltems collection.) The values of this item are calculated by using a formula.

The PivotField object has a Calculatedltems collection of all calculated items for that pivot field. To create a new calculated item, we use the Add method of the Calculatedltems object. This method has the same syntax as the Add method of the CalculatedFields object:

CalculatedltemsObject .Add(Name, Formula)

where Name is the name of the field and Formula is the formula for the field.

To illustrate, the following code adds a new calculated item to the Store Type pivot field:

ActiveSheet.PivotTables("Sales&Trans").

PivotFields("Store Type").CalculatedItems.

Add "CompanyX2", "='Store Type'Company*2"

The results are shown in Figure 20-42, where the calculated item is CompanyX2. The value in each of the CompanyX2 cells is twice the value in the corresponding Company cell.

Figure 20-42. Illustrating a calculated item (CompanyX2)

A

E

C

D

E

F

G

1

Year

(All)

z

Period

4

Store City

Store Type

Data

l

2

3

4

5

BOSTON

Company

Trans

2-3246

28714

28672

28602

6

Sale

44678

46927

46256

46223

1

Franchise

Trans

13993

13942

13275

13210

8

Sale

21816

21739

18632

18325

9

Company .X 2

Tran»

57423

57344

57204

10

Sale

'33356

93854

92512

92446

11

LOS ANGELES

Company

Trans

-,4588

35938

35692

35001

12

Sals

123473

1295*1

126340

12541 &

13

Franchise

Trans

71583

72947

756-19

74392

14

Sale

,-:>.I

268274

27ii.v2? 274531

15

Company\2

Trans

69176

71876

7 1364

70 002

10

Sale

246956

259128

252680

250836

17

NEW YORK

Company

Trans

24616

20104

V

20854

13

Sale

78089

35251

87905

88053

19

Franchise

Trans

5436 I

5132;

51218

2C

Sale

'

162461

17053?

' L ■'! 1

21

CompanyX?

Trans

49222

52208

54030

53703

22

Sale

-SC17S

- -i <;o2

175810

170116

For comparison, let us add a calculated field to the pivot table in Figure 20-42. We will add the same calculated field that we added when we discussed the CalculatedFields method earlier in the chapter:

With ActiveSheet.PivotTables("Sales&Trans"). CalculatedFields.Add("Average", "= Sales/Transactions")

.Orientation = xlDataField .Name = "Avg Check" .NumberFormat = "##.#" End With

The result is shown in Figure 20-43.

Figure 20-43. Illustrating a calculated field and calculated item m

10 11 12

16 17 13 13 20 21 22

25 20 27 23

Year

Period

Store- Ci'-y

Stone Typo

Data

1

2

3

4

BOSTON

Company

23248

28714

28672

236D2

Sate

44673

46927

46256

46223

Avg Check

1 6

1 6

1.6

1 6

Franchise

Trans

13993

13942

132.'t>

13210

Sale

21316

21739

18632

13325

Avg Check

1.6

1 6

1.4

1 4

Comp any X2

Trans

56496

57428

57344

57204

Sate

89356

93854

92'j 12

92446

Au-:; Check

1 6

1 6

1.6

1 6

LOS ANGELES

Company

Trans

34583

35938

35692

35001

Sale

123-178

129554

1263-10

125-118

Avg Chec;-.

3.6

36

3.5

3.6

Franchise

Trans

71583

723-17

7561G

74332

Sate

262431

268274

279325

274531

Avg Check

3 7

5 7

3 7

3 7

Comp any X2

Titinç

69176

71876

71384

70002

Sate

/4S956

259123

2576R0

75GS35

Avg Check

3 6

36

3 J.

3 6

MEW TORK

Comp an v

Trans

24*16

27015

26Ô54

Sate

730*9

8525 I

87905

83058

Avg Chi-tk

32

3 3

33

3 3

Franchise

Trans

53273

-34;.-: I

51322

51213

Sate

170353

182401

170537

167440

Avg Chech

3.3

3 1

3.3

3.3

Comp any X2

Trans

49232

5220S

^4ù 30

53!'08

Sale

156173

1705Û2

175310

176116

Avg Cheek

32

3 3

3.3

3 3

Note that the ListFormulas method of the PivotTable object will produce a list (on a separate worksheet) of all formulas in the pivot table. The outcome for the pivot table in Figure 20-43 is shown in Figure 20-44.

Figure 20-44. The output of ListFormulas

Figure 20-44. The output of ListFormulas

Let us conclude by recalling that the read-only IsCalculated property can be used to determine whether or not a pivot field or pivot item is calculated.

+1 0

Post a comment