pivot table calculated field - by broken out values in a column

[smakatura]

I know how to do normal calculated fields inside a pivot table and think what I want to do is not just a simple calculated field.

[TABLE="width: 413"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;" span="2"> <tbody>[TR]
[TD="width: 102, bgcolor: #B4C6E7"]Department[/TD]
[TD="width: 66, bgcolor: #B4C6E7"]Group[/TD]
[TD="width: 104, bgcolor: #B4C6E7"]Day[/TD]
[TD="width: 149, bgcolor: #B4C6E7"]Data Type[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]7[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]BE Hourly
[/TD]
[TD="bgcolor: #B4C6E7"]Chrysler[/TD]
[TD="bgcolor: #B4C6E7"]Current[/TD]
[TD="bgcolor: #B4C6E7"]Service Level - % 20
[/TD]
[TD="bgcolor: transparent, align: right"]100.00%[/TD]
[TD="bgcolor: transparent, align: right"]91.67%[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]BE Hourly
[/TD]
[TD="bgcolor: #B4C6E7"]Chrysler[/TD]
[TD="bgcolor: #B4C6E7"]Previous[/TD]
[TD="bgcolor: #B4C6E7"]Service Level - % 20[/TD]
[TD="bgcolor: transparent, align: right"]98.92%[/TD]
[TD="bgcolor: transparent, align: right"]91.67%[/TD]
[/TR]
</tbody>[/TABLE]



I want to produce a pivot table that shows the difference between 7's + 8's values. 7 and 8 represent the hour of the day that calls occurred. I have hours from 1 to 24. there are different types, groups and departments....so nota simple view when done


so base on he above values we can manually calculate the differences

[TABLE="width: 199"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Hour[/TD]
[TD="width: 64, bgcolor: transparent"]Previous [/TD]
[TD="width: 64, bgcolor: transparent"]Current[/TD]
[TD="width: 73, bgcolor: transparent"]Difference[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]98.92[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]-1.08[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]91.67[/TD]
[TD="bgcolor: transparent, align: right"]91.67[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


since the calculation would use "Day" column vest the values of Previous and current inside the day column, I think this is going to take yucky looking creativity to make happen.

any thoughts on how I cold get a pivot that looks like


[TABLE="width: 584"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="7"> <tbody>[TR]
[TD="width: 64, bgcolor: #B4C6E7"]Department
[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]Group[/TD]
[TD="width: 129, bgcolor: #B4C6E7"]Data Type[/TD]
[TD="width: 73, bgcolor: #B4C6E7"]1[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]2[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]3[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]4[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]5[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]6[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]7[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: #B4C6E7"]BE Hourly[/TD]
[TD="bgcolor: #B4C6E7"]Chrysler[/TD]
[TD="bgcolor: #B4C6E7"]Service Level - % 20[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]-1.08%[/TD]
[TD="bgcolor: transparent, align: right"]0.00%
[/TD]
[/TR]
</tbody>[/TABLE]

 

[smakatura]

the dates are not today vs yesterday, could be a week ago vs a week before that or a month ago vs a year before that. this will change with every time this pivot is run