Pivot Table Calculated Fields
 

Here is a sample of my data:

Date Employee Hours
6/1/2009 Steve 8.00
6/1/2009 Jack 5.50
6/1/2009 Steve 1.50
6/1/2009 Jack 1.50
6/1/2009 Mike 8.50
6/1/2009 Jack 2.50
6/2/2009 Steve 8.00
6/2/2009 Jack 7.00
6/2/2009 Mike 4.50
6/2/2009 Steve 1.50
6/2/2009 Jack 1.00
6/2/2009 Mike 4.50
6/3/2009 Steve 8.00
6/3/2009 Mike 8.00
6/3/2009 Jack 6.50
6/3/2009 Steve 1.50
6/3/2009 Mike 1.00
6/3/2009 Jack 3.00
6/4/2009 Steve 8.50
6/4/2009 Mike 9.50
6/4/2009 Jack 8.00
6/4/2009 Steve 1.50
6/4/2009 Jack 1.50
6/5/2009 Steve 8.00
6/5/2009 Jack 8.00
6/5/2009 Mike 6.50
6/5/2009 Mike 5.00

I created two Calculated Fields for a Pivot Table:
Calculated Field
Solve Order Field Formula
1 Regular Time =IF(Hours 40,40,Hours)
2 Overtime =IF(Hours 40,Hours-40,0)

Here is the Pivot Table:
Employee Data Total
Jack Sum of Regular Time 40.00
Sum of Overtime 4.50
Sum of Hours 44.50
Mike Sum of Regular Time 40.00
Sum of Overtime 7.50
Sum of Hours 47.50
Steve Sum of Regular Time 40.00
Sum of Overtime 6.50
Sum of Hours 46.50
Total Sum of Regular Time 40.00
Total Sum of Overtime 98.50
Total Sum of Hours 138.50

All figures for the individuals are correct and the Total Sum of Hours
at the bottom is correct, but the Total Sum of Regular Time s/b 120.00
and the Total Sum of Overtime s/b 18.50. What am I doing wrong?

Pivot Table Calculated Fields
 

Hi

can i know how to created two Calculated Fields in the pivot table?

so you add to the pivot table directly rather than adding 2 columne ?



"Slim Slender" wrote:

Here is a sample of my data:

Date Employee Hours
6/1/2009 Steve 8.00
6/1/2009 Jack 5.50
6/1/2009 Steve 1.50
6/1/2009 Jack 1.50
6/1/2009 Mike 8.50
6/1/2009 Jack 2.50
6/2/2009 Steve 8.00
6/2/2009 Jack 7.00
6/2/2009 Mike 4.50
6/2/2009 Steve 1.50
6/2/2009 Jack 1.00
6/2/2009 Mike 4.50
6/3/2009 Steve 8.00
6/3/2009 Mike 8.00
6/3/2009 Jack 6.50
6/3/2009 Steve 1.50
6/3/2009 Mike 1.00
6/3/2009 Jack 3.00
6/4/2009 Steve 8.50
6/4/2009 Mike 9.50
6/4/2009 Jack 8.00
6/4/2009 Steve 1.50
6/4/2009 Jack 1.50
6/5/2009 Steve 8.00
6/5/2009 Jack 8.00
6/5/2009 Mike 6.50
6/5/2009 Mike 5.00

I created two Calculated Fields for a Pivot Table:
Calculated Field
Solve Order Field Formula
1 Regular Time =IF(Hours 40,40,Hours)
2 Overtime =IF(Hours 40,Hours-40,0)

Here is the Pivot Table:
Employee Data Total
Jack Sum of Regular Time 40.00
Sum of Overtime 4.50
Sum of Hours 44.50
Mike Sum of Regular Time 40.00
Sum of Overtime 7.50
Sum of Hours 47.50
Steve Sum of Regular Time 40.00
Sum of Overtime 6.50
Sum of Hours 46.50
Total Sum of Regular Time 40.00
Total Sum of Overtime 98.50
Total Sum of Hours 138.50

All figures for the individuals are correct and the Total Sum of Hours
at the bottom is correct, but the Total Sum of Regular Time s/b 120.00
and the Total Sum of Overtime s/b 18.50. What am I doing wrong?
.