Skip to main content
Question

"Median" field using the new Query Formula Fields

  • June 30, 2023
  • 0 replies
  • 272 views
Forum|alt.badge.img+3

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?



------------------------------
Melissa Freel
------------------------------

    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? 



    ------------------------------
    Lordsman Burgos
    ------------------------------
    • Gary1
    • Quickbase Alumni
    • July 1, 2023

    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
    ------------------------------
    gary
    Forum|alt.badge.img+1
    • JohnErdmann
    • Quickbase Alumni
    • October 25, 2024

    rvalenzTo ensure order I recommend breaking the solution into 2 formula queries.
    1) A rank order query can be constructed with something like this in the target table: 

    Size(GetRecords("{3.LTE." & [Record ID#] & "}))

    This example ranks each record, where the lowest record ID gets a return value of 1, but you could use a similar approach on any fields.

    2) A formula to find the median based on rank order (doesn't need to live in target table, but can):

    // Number of records where the target field is not missing
var number n = Size(GetRecords("{3.XEX.}"));

// Middle index when n is an odd number
var number i = Round($n / 2);

// Lower and upper bounds for middle ranks of the target variable
var number lb = SumValues(GetRecords("{6.EX." & $i & "}"), 3);
var number ub = SumValues(GetRecords("{6.EX." & ($i + 1) & "}"), 3);

If(Mod($n, 2) = 1, 
    $lb, // n is odd
    ($lb + $ub) / 2) // n is even, so sum 2 middlest records and divide by 2



    Terms & ConditionsAccessibility statement