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
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)
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
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⌠)
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.
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
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.
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;
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.
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?
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.
)