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?
And 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.
