How do you do a running total to subtract quantity on hand from order balance in a baq?

I don’t know what you BAQ looks like… but you can do a running total like this (I used PartTran records in this example):

Add a calculated field:

sum(PartTran.TranQty) OVER (ORDER BY PartTran.TranDate)

Actually, in your case, you wanted to subtract… so again, using my own fields because I don’t know your BAQ set-up… if I wanted to SUBTRACT STK-CUS transactions from the running total, I would use this expression:

sum((case when PartTran.TranType=‘STK-CUS’ then PartTran.TranQty*-1 else PartTran.TranQty end)) OVER (ORDER BY PartTran.TranDate)

I’m still having trouble wrapping my head around how to subtract as it’s not an aggregate function. An example is I have 4 rows, all different Sales Orders, that I need to subtract the demand from the qoh in a running subtraction. Problem is it sees 4 repeating QOH values that get summed up. There is only 8pcs on hand not the sum of 32 which is 4 rows of 8.

Can you post your BAQ query so we can see what fields you’re using? That way I can play with the same fields this time around.

You have a lot going on in here, hahaha. Obviously I can’t recreate it in my database… but below is something I did to get a running qty which subtracts OrderQty…

**New Calc field Expression (RunQty):**

PartWhse.OnHandQty + sum((case when OrderDtl.OrderQty>0 then OrderDtl.OrderQty*-1 else OrderDtl.OrderQty end)) OVER (ORDER BY OrderDtl.RequestDate)

**Results of my test query:**

Why would you not partition by part number?