Anyone come up with a simple way to calculate a median of a field. I am thinking of using the
"new" query formulas with GetRecords, etc. Anyone solve this already?
Hello!
You mean like the median of a field across multiple records, or the median of other fields in a single record, condensed and measured in another field in that same record?
Qrew LegendAnd do you mean Median, or will you settle for Average.
Mark: Yes, I mean median. I already used a query formula field to come up with an average.
Lordsman: I want to come up with a median of one field ACROSS multiple records. So I thought the query fields would help.
This formula gives me a textlist of the values I need and sorts them from low to high which is first step of calculating a median. I have two formula check boxes that create a filter in QueryOne. QueryTwo returns the list of values I need from field ID 2393. Now, I just need to have QB tell me what the middle value is:
var text QueryOne="{2030.EX.'True'} AND {4462.EX.'True'}";
var textlist QueryTwo=GetFieldValues(GetRecords($QueryOne,[_DBID_CUSTOMERS]), 2393);
Thoughts??
This is a formula for a numeric field that will evaluate the median value of another numeric field in the same table. It only accounts for non-zero values.
If you want to generate the median of a numeric field on a different table, you'll have to refactor the $arrayAsText formula query, but that shouldn't be difficult if you already have your query working.
I'm not a huge fan of formula queries because of their performance, so I can't promise this will work on a massive table with hundreds or thousands of unique values.
I tested it using less than 20 records and it returned the expected results. Depending on how many records you have, you should test it out to ensure it's working as expected.
var text arrayAsText = SearchAndReplace(ToText(GetFieldValues(GetRecords("{3.GT.0}"), 16)), " ", ""); var textlist array = Split($arrayAsText); var number arrayLength = Size($array); var number modulo = Mod($arrayLength, 2); var number median = If ( $modulo != 0, ToNumber(Part($arrayAsText, Ceil($arrayLength / 2), ";")), (ToNumber(Part($arrayAsText, $arrayLength / 2, ";")) + ToNumber(Part($arrayAsText, ($arrayLength / 2) + 1, ";"))) / 2 ); $medianGary:
Thanks, works like a charm and I don't forsee super large data sets as I have "filtered" appropriately.
Hi,
Thank you for this query solution. i hope one day it becomes a native QB features.
I followed the format, and works most of the time. when it doesn't work, it seems to use a number higher or lower than the median, which makes me believe, that the numbers aren't sorting in order.
how can the list of values be correctly ordered?
Thanks.