PartTran BAQ: Calculating the "Running Total" of the History Tracker

Epicor ERP 10
, ,
21

Ignore the Before and After Qty’s. You’ll have to use some moderately tricky techniques in the BAQ to make a running sum.

Something like a “Sum over Partiton” Search this site there’s some examples on running sum.

22

@Alice_Elizabeth

Make a CalcField SignedTranQty with expression

(case  
    when PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1 
    when PartTran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')  then 1 
    else 0
end) * PartTran.TranQty

Then another Calc field RunningQOH with the expression

SUM(SignedTranQty) OVER (Order By PartTran.TranNum)

Edit

You don’t need to select the GroupBy box on the other fields like you would with a plain SUM() function.

7 Likes
23

I just had to write this for a report. This gives you a running total per PartTran.TranNum for transactions grouped by company, part and lot. Also, I am not considering all TranTypes or Plants. The big thing that you want is the ROWS UNBOUNDED PRECEDING at the end of your statement.

SUM(CASE WHEN PartTran.TranClass IN (‘A’,‘R’) THEN PartTran.TranQty1 WHEN PartTran.TranClass IN (‘I’) THEN -1PartTran.TranQty END) OVER (PARTITION BY PartTran.Company, PartTran.PartNum, PartTran.LotNum ORDER BY PartTran.TranNum ROWS UNBOUNDED PRECEDING)

I have this in a Subquery in the BAQ and then join to the top level query by TranNum. I then apply a Table Criteria for the Subquery to filter the records to the top level records because WHERE’s are a lot faster than joins in all of my tests.

image
image807×148 3.41 KB

3 Likes
24

Thank you, I will try this!

25

Hey Alice,

Looks like others got here before me, but yes, there are TranTypes that need to be set to negative to get the calculations right. I marked @ckrusen post as solution.

1 Like
26

Just a follow-up. I used

SUM(SignedTranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)

to make the subscribe function in grid work. Thank you all!

1 Like