Calculated field of Pivot Table

Hello,

I have to add two calculated field to my pivot table. I know how to add calculated filed. But my problem is that , I want to make one field dependent on the other. Let me explain in detail. I have database of Receipts and Payments for each day as follows.

Date Receipt Payment A/cHead
01/04/2007 2500 Receipt
15/04/2007 100 Courier
27/04/2007 110 Conveyance
05/05/2007 150 Conveyance
12/05/2007 10 Xerox
27/05/2007 1000 Receipt
06/06/2007 200 Conveyance
16/06/2007 15 Xerox
30/06/2007 35 Entertainment

I want to have Opening Balance and Closing Balance for each date in pivot table. I have created Pivot Table with ClosingBalance Field. now I want to add field OpeningBalance. Problem is that ClosingBalance for previous date is OpeningBalance of next date. How to go about this?

Please Hlep me. I have also attached my sample file.

Originally Posted by vandanavai

Hello,

I have to add two calculated field to my pivot table. I know how to add calculated filed. But my problem is that , I want to make one field dependent on the other. Let me explain in detail. I have database of Receipts and Payments for each day as follows.

Date Receipt Payment A/cHead
01/04/2007 2500 Receipt
15/04/2007 100 Courier
27/04/2007 110 Conveyance
05/05/2007 150 Conveyance
12/05/2007 10 Xerox
27/05/2007 1000 Receipt
06/06/2007 200 Conveyance
16/06/2007 15 Xerox
30/06/2007 35 Entertainment

I want to have Opening Balance and Closing Balance for each date in pivot table. I have created Pivot Table with ClosingBalance Field. now I want to add field OpeningBalance. Problem is that ClosingBalance for previous date is OpeningBalance of next date. How to go about this?

Please Hlep me. I have also attached my sample file.

I think you'd be better advised to hold the opening and closing balances as two extra columns in your data. e.g with E1 = "Op Bal" and F1 = Clos Bal

E2: =IF(F1="Clos Bal",0,F1)
F2: =E2+B2-C2

Then copy down and use these fields in your pivot table rather than trying to calculate an opening balance.

Rgds