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)
1 Like
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)
1 Like
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:
1 Like
Why would you not partition by part number?