monthly opening and closing balance in pivot table

[shripaldalal]

hi,

have a pivot table that gives a bank statement:

1-apr opening bal 5000 dr
2-apr withdrew cash 2000 cr
3-apr chq to creditor 1000 cr

how do i get a new line automatically in the pivot:

1-apr opening bal 5000 dr
2-apr withdrew cash 2000 cr
3-apr chq to creditor 1000 cr
30-apr closing bal 2000 dr (that is total debits - total credits for
THIS MONTH OF APR ONLY)

how do i get the above last row automatically in a pivot, without
having it in source data, something like a calculated item or
something ? is it possible ?

best regards,
shripal d.

 

[Roger Govier]

Hi

Assuming your data is currently in column A:C, add an extra column and in D1
enter Month
In D2 enter
=IF(A2="","",TEXT(A2,"mmm"))
Copy down as far as required
Add a new column E and enter in E1 Amount
In E2 enter
=IF(C2="","",IF(RIGHT(C2,2)="dr",LEFT(C2,LEN(C2)-3)*-1,LEFT(C2,LEN(C2)-3)*1))
copy down as far as required.

Extend the source for your PT to include these new columns
Substitute your new column E, Amount, in place of your exiting column C
heading in the Data section.
Add the new Month column to the Page area

Select Apr from the page dropdown, and the Total will be the Closing Balance
for April
You can Overtype the cell entry Total Value with Closing Balance if you wish

 

[shripaldalal]

Well that wont solve my problem

I am saying something else. Total has to be zero

apr-1 opn bal 1000 dr
apr-2 cash 200 cr
apr-3 recvd 200 dr
apr-4 cl bal 1000 cr
total 1200 dr and 1200 cr (both equal)

dr and cr are actually two columns, i have made them one column here
because the formatting is a nightmare
so basically apr-4 line is not in the data, it is added automatically
in the pivot, is this possible ?

best regards,
shripal.

 

[shripaldalal]

Well that wont solve my problem

I am saying something else. Total has to be zero

apr-1 opn bal 1000 dr
apr-2 cash     200  cr
apr-3 recvd    200 dr
apr-4 cl bal   1000 cr
total              1200 dr and 1200 cr (both equal)

dr and cr are actually two columns, i have made them one column here
because the formatting is a nightmare
so basically apr-4 line is not in the data, it is added automatically
in the pivot, is this possible ?

best regards,
shripal.

sorry total does not have to be zero but equal

 

[Fred Smith]

The options that I see are:

1. Use just one column for your amount. Positive numbers are debits, and
negative numbers are credits.
2. Add a caculated field to your privot table (Dr + Cr) and sum that.

Regards,
Fred.

Well that wont solve my problem

I am saying something else. Total has to be zero

apr-1 opn bal 1000 dr
apr-2 cash 200 cr
apr-3 recvd 200 dr
apr-4 cl bal 1000 cr
total 1200 dr and 1200 cr (both equal)

dr and cr are actually two columns, i have made them one column here
because the formatting is a nightmare
so basically apr-4 line is not in the data, it is added automatically
in the pivot, is this possible ?

best regards,
shripal.

sorry total does not have to be zero but equal