Perform a calculation on BAQ Summary Fields

Simple question - I would believe
I have a BAQ in which I wish to perform a calculation on two or more BAQ Summary Fields
For example I have a QTY field and a COST field and I KNOW I can create a calculated field and get the extension.
But I wish to see how I can calculate the extension of the SUMMARY values from the SUMMARY options values.
Surely the summary option would need to be fixed ON - and the calculation in place (in BAQ or DSHBD)

I would expect this has been done, just looking for a hint/example

if you are talking E10, then you can do it by:
sum(qty * unitprice), and it will do the math first, and then sum it.

1 Like

thanks for the reply.

Your answer is correct. I could make a calculated field EXTENSION and SUM IT at the group
.
but i surely did not state my issue well
I am really looking for a group calculation on the summarized group totals
For example
Here i wish to see the GROUP PerUnit/Cost (not the average)

Qty || Cost || Cost/Per (calculated as QTY / COST)
oooooooooooooooooooooooooooooooooooooooooooooooooo
80 || 800.00 || 10.00
80 || 800.00 || 10.00
80 || 800.00 || 10.00
10 || 10.00 || 1.00
oooooooooooooooooooooooooooooooooooooooooooooooooo
sum || sum || Average
oooooooooooooooooooooooooooooooooooooooooooooooooo
250 || 2410 || 7.75 = (10 + 10 + 10 + 1) / 4

so instead of averaging the Cost/Per’s

i want a weighted Cost/Per for the group as
oooooooooooooooooooooooooooooooooooooooooooooooooo
80 || 800.00 || 10.00
80 || 800.00 || 10.00
80 || 800.00 || 10.00
10 || 10.00 || 1.00
oooooooooooooooooooooooooooooooooooooooooooooooooo
sum || sum || (group calculation sum of cost / sum of qty)
oooooooooooooooooooooooooooooooooooooooooooooooooo
250 || 2410.00 || 9.64 = (2410/ 250)

but i can’t seem to get this in a simple BAQ

(pardon the multiple edits to correct my math - i was a math major at one point - not sure what happened :slight_smile:

My wife says there are two kinds of people, letter math people and number math people. If you were a math major you are probably a letter math person, which means actually doing math with numbers is hard. (I’m a letter math person too…:wink: )

2 Likes

I think you just need another level of sub-query. Do all of the summing in one, then in the next level up, do the division (like you do in the example). You won’t end up using the average function.

1 Like

my gutt tells me (without going the BAQReport/SSRS route) that this is not possible using any combination of baq / subquery or calculated field magic - but requires a dashboard customization to access the summary totals.

i imagine somewhere, somehow, this was already been done. and would love to see an example.
if someone is willing to share

regards

And if you use the subquery method, you will have to define the grouping at the BAQ level, so it won’t be dynamic. Is that what you are after?

What are you wanting to group by here?
image

the grouping would be by Part, Vendor or a similar entity like SO or Project
in the case i am looking at the dashboard grouping will not change.

I just don’t yet recognize how the sub-query gives me my result … but i will try it some.

it is possible. just start posting your query phrase when you get stuck.

You won’t be able to do the grouping in a grid, (that’s what I mean by not dynamic) But you can group them in the BAQ.

Your first sub will be what you have on the top, qty, price, and the extension for qty*price. Then you need the fields for what you want to group by.

the next sub will bring that as a table in and group by your criteria, summing up the quantities and the extended prices.

the next level up will bring in the summed up quantities and extended prices, and make a calculated field to divide them (like you have in your example)

It can probably be done with fewer levels, but that’s how I would attack it.

Hi Banderson,
i am not sure if i understand what you mean here, but let me suggest this -please correct me if i am wrong- you want the weighted average unit cost instead of the normal average for the unit cost counting the factor of produced qty NOT based on the occurrence no., i.e. you want to give more weight to the cost of 80-off more than the cost of 10-off, then do not calculate the average at the lower level, only get the two totals and do the avarage calculation on the higher level.

Lower level Query
Qty || Cost || Cost/Per Record(calculated as QTY / COST)
oooooooooooooooooooooooooooooooooooooooooooooooooo
80 || 800.00 || 10.00
80 || 800.00 || 10.00
80 || 800.00 || 10.00
10 || 10.00 || 1.00

Top Level Query Grouped by your criteria let say PartNum
oooooooooooooooooooooooooooooooooooooooooooooooooo
PartNum || TTL Produced Qty || TTL Cost || Average Cost/Per Unit for all records (calculated as TTL Cost / TTL Produced Qty)
oooooooooooooooooooooooooooooooooooooooooooooooooo
250 || 2410.00 || 9.64 = (2410/ 250)
oooooooooooooooooooooooooooooooooooooooooooooooooo

I think you might be able to do this with a window function query. You can see an example with the system BAQ named: zGSTR1-B2B_InvcDtl_IN . If you change the ICE.QueryHdr.CGCode to an empty string you can load the BAQ in the designer and see how Epicor created it. What you want is to sum it over the group something like:

SELECT CustNum, OrderNum, PartNum,
SUM(UnitPrice*OrderQty) OVER(PARTITION BY PartNum) AS SubTotal
FROM ERP.OrderDtl;

There is a ton of information on window functions here: https://windowfunctions.com/ also on Pluralsight or even Youtube Window functions in SQL Server - YouTube

3 Likes

correct
I don’t want an average of the averages
I want an average of the totals

since an average of the averages applied the same ‘weight’ to each occurrence

What are you actually wanting to display in the end result?

You can get what you want, I believe, by sub-query within sub-query. One to sum everything you want and return a single line, then an outer query to calculate on those totaled results. I wonder if your problem is that you actually want to see the rows that lead to the result?

If so, you can keep them in a BAQ by having a further query for the rows alone, again, and doing a union with the total.

Or, as you say, depending what your end goal is, you can do your calculations in a customization, which we’ve done a few times when people want to be able to see different results depending on their filtering.

This appears to be something like the coding customization option
OK - I will try to look into this
thanks

1 Like

this sounds like an the uncustomizied option
OK thanks - I will look into this as well.
and reply back for all to see

Just checking - you talk about BAQs, but the way you mention summaries sounds like you’re meaning more specifically the results of a BAQ in a grid, when you summarise using the headers. In which case the answers are going to be quite different because the logic isn’t happening in the BAQ at all.

that’s exactly what I mean
the affects when you choose to “show summaries” and select average or sum on those columns
although IF combining multiple queries - without using “summaries” provides the same result -why not?

you should be able to do this. we have a few queries that sum/avg/whatever. then there is a main query to show the results.

As I understand it, when you summarise a grid, each column is totally independent.

In the BAQ itself, it’s perfectly possible to divide the sum of one column by the sum of another and use the resulting weighted average, but as you see, to be able to return the same thing in a summary means you need to provide a separate column to calculate on. It feels kind of unlikely you can provide a figure which will make sense to users on each line yet summarise to give the result you want.