Cumulative Calculation 1

[northw]

Hi All,

I have a question regarding cumulative calculation where I want to calculate row by row Invoice and Payment.

Here is the sample data and my desired output.

Cust/Month/Trans/Amount/CumulativeBal(my desired output)
1, 1, Due, 100, 100
1, 1, Payment, 50, 50
1, 1, Payment, 50, 0

1, 2, Due, 500, 500
1, 2, Payment, 500, 0

1, 3, Due, 2000, 2000
1, 3, Payment, -400, 2400
1, 3, payment, 100, 2300,
1, 3, payment, 2300, 0

1, 4, Due, 800, 800
1, 4, Due, 200, 1000
1, 4, Payment, 1000, 0

There are no groups for the dataset, the report will pull only one customer at a time.

Any help/direction is appreciated.

Thanks!

 

[bdreed35]

Could handle this a couple of different ways. Here is one.

-Create a formula and call it "Amount for Cum Bal"
-Use this logic for the formula:

if {Table.Trans} = "Payment"
then {Table.Amount} * -1
else {Table.Amount}

-Create a new Running Total field that summarizes the formula above, evaluates each record, and never resets
-Place the running total field on your report after the other columns, assuming that they are already there. You might need to add some sorts on Cust, Month, and Trans

~Brian