If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

how to calculate running total based on two columns please....

Started by bujji, 10 Feb 2009 12:12:23 PM

Previous topic - Next topic

bujji

Hello All,

can you please give me a help hand regarding to the below problem

presently i have a report with structure as below in report studio (8.3)

TransactionDate,Detail,City,Amount,OldBalNov 1, 2008,paybill for x-x-11-2003,win,-1200.73,710.00
Nov 3, 2008,Call Card,win,33.45,710.00
Nov 3, 2008,Insured Amount,ATL,55.22,710.00
Nov 5, 2008,intercom transfer,win,47.98,710.00
Nov 7, 2008,BOA disbursement,NYC,723,710.00
Nov 7, 2008,Incoming BOA Chicago,CHG,-105.56,710.00
Nov 7, 2008,seychellis air forth,DC,675,710.00
.......
........
.........
Nov 30, 2008,Kennedy air tranfer,NYC,-9805.56,710.00



oldbal is always same because it is single amount,
now how can i add running total of amount, with initial value as oldbal

for ex: in above example 710 is old bal, so 710+ (-1200.73) so the values in oldbal like below

TransactionDate,Detail,City,Amount,OldBal
Nov 1, 2008,paybill for x-x-11-2003,win,-1200.73,710.00
Nov 3, 2008,Call Card,win,33.45,-490.00
Nov 3, 2008,Insured Amount,ATL,55.22,-435.51
Nov 5, 2008,intercom transfer,win,47.98,-387.53
Nov 7, 2008,BOA disbursement,NYC,723,335.47
Nov 7, 2008,Incoming BOA Chicago,CHG,-105.56,229.91
Nov 7, 2008,seychellis air forth,DC,675,904.91


how can i achieve this above oldbal please

hardly tried with different running-total,moving-total but some where else i am doing something wrong

please help me

Grateful to all of you

Best Regards
asini

bujji


Jurii

hi Bujji,

Try to create the query calculation:
running-total([Amount])+[OldBal]

bujji

Hello All,

kindly any ideas please

basically the problem is need to calculate running total for a column (initiat start is stored in another dataitem)

this initial data item contains only one value 

bujji

Hello Jurri,

thank you very much for your post
i created a dataitem and tried the below way, but what it is doing here is everytime it is adding 700(openbal) to every row value.
it is not working as it need jurri,,

running-total([Amount])+[OldBal]

Regards
bujj

Jurii

And what about this result in the last column? Is it correct?

OrderNumber OrderDate OrderQty OldBal NewItem
10022 13.11.2004 -1 310 710 710
10024 13.11.2004 -1 320 710 -600
10029 20.11.2004 -1 500 710 -1 920
10035 27.11.2004 -910 710 -3 420
10041 03.12.2004 -1 950 710 -4 330
10049 13.12.2004 -1 640 710 -6 280

Jurii

This result was achieved using the following formula:

running-total([Order Qty]) + [OldBal] - [Order Qty]

bujji

Hello Jurri,

thank you very much for your helping nature.,

And what about this result in the last column? Is it correct?
no, its not it is everytime increasing 710+710+.......

but now i tried with
running-total([Order Qty]) + [OldBal] - [Order Qty]

it is giving results fine, i am on half the way,

-----------------
one more doubt here is, in my report i need to show report sub totals, group by Transaction date.

while i am doing group by date, Balance column sub totals needs to be just previous value.
is there any possibl way to show in the sub totals (just previous value of balance column)

i.e

TransactionDate,Detail,City,Amount,OldBal
Nov 1, 2008,paybill for x-x-11-2003,win,-1200.73,710.00
group by here----------------------------------
Nov 3, 2008,Call Card,win,33.45,-490.00
Nov 3, 2008,Insured Amount,ATL,55.22,-435.51
--------------------------- here previous value means -435.51
Nov 5, 2008,intercom transfer,win,47.98,-387.53
----------------------------here previous value means -387.53
Nov 7, 2008,BOA disbursement,NYC,723,335.47
Nov 7, 2008,Incoming BOA Chicago,CHG,-105.56,229.91
Nov 7, 2008,seychellis air forth,DC,675,904.91
---------------------------------here 904.91
-----------------
Jurri, really you gave me a good hand buddie,
i Grateful to you my dear master

Thanks & Regards
bujji