Running Total Evaluate

[AlanDI1]

I have a series of orders for some products. There are multiple rows per order sorted by date. The status changes in each row. I am trying to figure out whether an order is closed (sold) or open (on order) in the last row. Then sum the quantity sold or the quantity on order by product.

Item Order Date Status Qty
A 1 1/1/09 20 15
A 1 1/2/09 25 15
A 1 1/3/09 60 14

A 2 1/1/09 20 32

A 3 1/1/09 20 21
A 3 1/2/09 35 21

B 4 1/1/09 10 12


Status 60 is Sold. The summary for product 'A' should be 14 sold & 53 on order. I created a 'Sold' field & a 'OnOder' field. The are:

Sold:
IF {status} = 60 Then
{Qty}
ELSE
0

OnOrder:
IF {status} < 60 Then
{Qty}
ELSE
0

Then I create a Running Total for each to evaluate on change of 'Order' and to reset on change of 'Item'. Except that does not seem to get me the correct totals. If I view the values for 'Sold' & 'OnOrder' in each row they are correct but in the running totals they do not seem to be using the correct status or they are using the status on the previous row, I am not sure.

Any ideas? Thanks for your help.

 

[lbass]

Try sorting your records in descending order by date. Then "on change of" order will pick up the most recent status.

-LB

 

[AlanDI1]

Great lbass. I was sorting ascending and trying to pick it off the bottom. Tried sorting descending as you suggested and picked it off the top. Never would have thought of that, Thanks for your help.