Pivot table calculated item across records

[John Michl]

Can a calculated item work across multiple records or only one record?

I have a customer database that includes Customer, Year, Type (among other
things). Type is a code that refers to one of three "types" which are new,
retained, lost.. New means it is a new customer in that year, retained
means the customer is active in the current year and prior year and lost
means that the customer had no sales in the current year but was active in
the previous year.

Cust# Year Type
ABC 2001 New
ABC 2002 Retained
XYZ 2001 Retained
XYZ 2002 Lost
LMN 2001 Retained
LMN 2002 Retained

A pivot table might look like:
Count of Type: 2001 2002
New 1 0
Retained 2 2
Lost 0 1


I want to calculate the percent retained in a given year. To do this I need
to add up counts for certain values within a year. The formula is Retained
customers/(Active customers in previous year). Active Customers in Previous
Year can be calculated by adding the Retained and Lost in the current year.
With the example above:

Year 2002
Retained: 2
Lost: 1
Active in 2001 = 2 + 1 = 3
Retention equals 2/3 or 67%

I want to create a calculated item/field in the pivot that does this but I
think that calculated items do math related to fields not values for a
particular field. My goal is to get to a pivot table that looks like:

Retain% 1999 2000 2001 2002
East 55% 56% 58% 60%
Central 45% 50% 52% 62%
West 60% 65% 70% 75%


Any ideas?

Thanks - John