How to (kind of) Sum a Sum in a Calculated field

Epicor ERP 10
, ,
1

Looking for a wee guide again (what’s new!).

I have a BAQ which is meant to be showing Parts OnOrder (but not for Demand) and also OnHand stock.
My issue for today is that I have created a Subquery for Onhand with a Calculated field:

image
image713×286 3.1 KB

However, when I select this Calculated field in the main query, as the part sometimes appears in more than one Warehouse location, I sometimes get multiple lines for a part so I would like to present just a single total of OnHand quantity of a part regardless of warehouse location.

I have wrestled with this trying to Sum the results of the original subquery but I hit various grouping and aggregate type errors.

Can this be done? cheers.

1 Like
2

Yes, this is totally doable. Show us your BAQ, I’m sure we can get it working.

2 Likes
3

I have tried removing Warehouse and PO Line but still the same results
GA_PartOnHand-OnOrder.baq (14.0 KB)

4

The biggest step is to get warehouse out of the grouping. That allows the sum to sum across more than one warehouse. I made a few other changes so that I could run the BAQ. This should be a good start. You have to put your filters back in.
I hope this helps!
GA_PartOnHand-OnOrder_N.baq (11.4 KB)

1 Like
5

That look really good Nate, thanks!
I suppose I over complicated the BAQ most likely to begin with.

I will put the filters back in and check how it looks for the Buyers and see if it gives them what they were looking for :+1:

1 Like
6

It’s the grouping. If you group by part only, it will sum everything for that part and you won’t get duplicate rows. You could potentially get doubled sums, but that’s a join issue.

2 Likes