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.
