pivot table running total problem
 

Hi,

Have rows:
Product, transaction no


have data fields:
qty rec, qty iss, balance (calculated field: qty rec - qty iss)


want balance to be a running total with base field of product


so want something like:


================================================== =====
product-----doc no--------rec---------- iss-------balance
================================================== =====


acetic acid
--------opnbal/1--------4-----------0---------- (4-0)=4
--------sales/inv--------0----------3----------- (4-3)=1
acetic acid total:----4-----------3----------*----------1


(now again running total should start from 0 for new product)


ammonium nitrate
-------opnbal/1-------22----------0----------- (22-0)=22
-------sales/inv--------0----------16---------- (22-16)=6
product total--------22----------16---------------------*-6


Instead of that what i get right now is:


acetic acid
------opnbal/1---------4------------0-----------(4-0)= 4
------sales/inv---------0------------3----------(0-3)= -3
acetic acid total-----4------------3----------*----------1


(now again running total should start from 0 for new product)


ammonium nitrate
------nbal/1-------------22---------0----------(22-0)= 22
------sales/inv-----------0-------16--------- (0-16)= -16
product total----------22-------16-------- 7 [22-16=6]+[4-3=1]


any suggestions if something can be done ?


thanks.
shripal.