Pivot table - creating a calculated field

[ihorwitz]

I am trying to create a calculated field without success.

The underlying pivot table data is as follows:

FISC SKU COM VOL UNIT_COST
2008 151 173 155 8.92
2008 200 20 145 0.59
2008 200 90 145 0.49
2008 200 100 145 0.20
2009 151 173 139 8.46
2009 200 20 130 0.56
2009 200 90 130 0.46
2009 200 100 130 0.19

The pivot table is designed as follows:

Data FISC
VOL UNIT_COST
SKU COM 2008 2009 2008 2009
151 173 155 139 8.92 8.46
200 20 145 130 0.59 0.56
90 145 130 0.49 0.46
100 145 130 0.20 0.19

I would like to create a calculated field that does the following calculation:
=(UNIT_COST for 2009 - UNIT_COST for 2008) x VOL for 2009

Can anyone help?

Thanks

 

[Fazza]

Hi,

If you have data only for 2008 & 2009,

Iif(FISC=2008,-1,1)*VOL AS [NewField]

Though a better approach might be

Iif(FISC=2008,-1,Iif(FISC=2009,1,0)*VOL AS [NewField]

Use these in the SQL between the source data and the pivot table. For example when creating the pivot table take the 'external data source' at the first step of the wizard, or, via menu data, import external data, new database query. If you don't have a non-dynamic named range for the data & get the message about no visible cells, OK to it and then go 'options' and 'system tables'. OK to this and see the worksheets listed. The actual change is done in MS Query - hit the SQL button and edit the SQL there. (In the finished pivot table, place the new field as a data field and sum on it.) HTH, Fazza