Advanced Aggregates for BAQ (Median, Standard Deviation, Quartiles, Etc.)

baq

(Evan Dlugopolski) #1

I am trying to create a BAQ that will summarize job and labor dtl data by part numbers. I am fairly well versed in creating BAQs and I understand SQL for the most part but I do not have access directly editing SQL.

I want to aggregate data for example to find the median job qty, quartiles, and standard deviation, for a given part number. I was trying to play around with the PERCENTILE_CONT function and epicor doesnt seem to reject the syntax but I am missing something.

As i understand it SQL syntax generally doesn’t have a function for median and i know Epicor doesnt have it in the fucntion editor. Not sure about if there is an easy way to aggregate standard deviation as well.

It has to be pretty common that users need to aggregate by more that just your standard avg, sum, min, max, etc. Hoping someone can shed some light on this!

Thanks!

Evan


(John Mitchell) #2

I found a blog article a while ago that helped explain a lot of it for me ( @Rick_Bird )


(Evan Dlugopolski) #3

Thanks! I found the same blog and it did help quite a bit.

I was able to get things working the way I wanted with “stdev([ Value Field ]) OVER (PARTITION BY [Subgroup Field])” and “PERCENTILE_CONT( 0.5 ) WITHIN GROUP ( ORDER BY [Value Field] ) OVER ( PARTITION BY [Subgroup Field])”

Edit (For other users looking to learn more):

Only thing to keep in mind with this method is the aggregation is done in within the same table as the source data. I was making the mistake of trying to put these values in an aggregate calculation using the group by.

So for example the table below with a calculated field “Mean Aggregate” = avg(Value) OVER (PARTITION BY Group) would look like this. this would just go in a normal calculated field. Do not us group by on the group field in the query.

image

Thanks,
Evan


(John Mitchell) #4

SQL is limited in it’s statistical functions but if you have SQL 2016 you can run R scripts on the data as well. I would be very very surprised if we don’t see something in Nashville about Epicor using it for machine learning or artificial intelligence. It’s just way to hot right now.

Here is a demo combining R and SQL. https://blogs.msdn.microsoft.com/sql_server_team/a-walkthrough-of-loan-classification-using-sql-server-2016-r-services/