"Median" field using the new Query Formula Fields

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

Gary1

Qrew Assistant Captain

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

);

$median

------------------------------
gary
------------------------------

rvalenz

Qrew Member

8 months ago

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.

Forum Discussion

"Median" field using the new Query Formula Fields