Creating a Running Total formula to create a decreasing value

[helenaapw]

I have a report where I am trying to generate a reducing value running total. The report is grouped by Part No, each Part no has a field which contains a value for Qty on Hand, (this value comes from an embedded IAL). The detail part of the report shows transaction qtys (this is a formula)which I would like to net off the Qty on hand field, I also want to add a formula to calculate the offset qty. This is important when you are only partially using the qty on a transaction to get to 0. As per line 4 below
Example
Part No 123XYZ
Qty on hand 430

Details
Transaction Qty 100 Running Total Formula 330 Qty offset 100
Transaction Qty 150 Running Total Formula 180 Qty offset 150
Transaction Qty 125 Running Total Formula 55 Qty offset 125
Transaction Qty 150 Running Total Formula 0 Qty offset 55
Transaction Qty 300 Running Total Formula 0 Qty offset 0
Transaction Qty 149 Running Total Formula 0 Qty offset 0

 

[Madawc]

I do't quite follow what you want. But I'd say you needed a formula that made use of a running total.

Note that you can subtract running totals from summary totals. So if you had a group of unknown size, you could count it using a summary total and also a running total. For each line, subtract the running total from the summary total to see how many more lines there would be in the group.

Madawc Williams (East Anglia, UK)

 

[helenaapw]

Can you give me some guidance on how to set up the running total formula. I have looked at the Crystal help on this one and I couldn't follow it

 

[lbass]

I think you could use a running total {#transqty} of {table.transactionqty} where you sum {table.transactionqty}, evaluate for each record, reset on change of group (PartNo), and then use a formula {@netqty}:

if {table.qtyonhand} - {#transqty} > 0 then
{table.qtyonhand} - {#transqty} else 0

For {@qtyoffset}, try:

if {@netqty} > 0 then {table.transactionqty} else
{table.qtyonhand} - ({#transqty} - {table.transactionqty})

-LB