Advanced Pivot Table

[anuu_radhaa]

Hi Guys,

I am trying to prepare a pivot table wherein I am not sure where I am
missing my eyeballs


The first tab sheet contains data as below.


SBU, Domain, project,Emp#, Empname, exp in years, exp in months, exp
total, range
S1,D1,P1,E1,N1,8,11,8.92,4+ Yrs
S1,D1,P1,E2,N2,6,7,6.58,4+ Yrs
S1,D1,P2,E3,N3,3,7,3.58,2-4 Yrs
S1,D1,P2,E4,N4,2,7,2.58,2-4 Yrs


NOTE: 1. the data is delimited by comma.
2. all the data is pulled from SAP BW
3. the experience is captured in years and in months
4. formula for the column 'exp total' is defined as
(F2*12+G2)/12; where F2 = 'exp in years', G2 = 'exp in
months'
5. formula for range column is defined as:
IF(H2>4,"4+ Yrs",IF(H2>2,"2-4 Yrs", "0-2 Yrs"))


The pivot table is created with the range and the layout is defined as:

ROW = SBU, DOMAIN, PROJECT
COLUMN = RANGE
DATE = COUNT OF RANGE


The Pivot table gets created without any problem. Till here everything
is fine.


Now the requirement is to provide avegrage years of experience per
project in the column. The formula will be total of 'exp total' /
number of employees per project. This means, for Project P1 the avg
years of exp will be
(8.92 + 6.58) / 2 = 7.75


Is there a way where I could add this information also?


This is the output which I am looking for


-------------------------------------------------------------
Count of range range
SBU Domain project 2-4 Yrs 4+ Yrs Avg Years
S1 D1 P1 2 7.75
P2 2 3.09
D1 Total 2 2
S1 Total 2 2


-------------------------------------------------------------


Any help and advise is appreciated.


Thank you in advance,
Anu

 

[s boak]

Anu:
It seems to me that adding a calulcated field might do what you need ??
Click inside the table - left click the Pivot Table button on the PT
Toolbar - select Formulas - Calculated Field
This Link may help:
http://office.microsoft.com/en-us/assistance/HA010346331033.aspx#CompareYourCustomers

HTH
Steve