Median of Field Entries

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
I need to be able to calculate the median of numeric fields.  I know that we aren't able to do it natively so I'm hoping that someone from the community has had to do this before and can help.  There's an old post that references an older java site but its no longer available.

Basically, my organization only calculates the time between 2 dates and then gathers all those durations and finds the median.  This is the only metrics they go by. 
Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of John Rogers

John Rogers

  • 340 Points 250 badge 2x thumb
On average how many numbers are you talking about finding the medium of? Because this would be a VERY complicated process to do in Quickbase without using javascript.

A type of vanilla bubble sorting. Are you trying not to use javascript? 




Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb
It will vary as it will need to be scalable the more entries we put into it.  And No i would LOVE to use javascript, I've been practicing with onload on other apps and I can get that function to work but with the median, I would need to know what to put and where.
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Just organize a bunch of folk dancers to do the sorting

















(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Apparently the Insert-sort with Romanian folk dance is the fastest algorithm at four minutes and three seconds so I would go with that rather than the Bubble-sort with Hungarian ("Csángó") folk dance which comes in at five minutes and fifteen seconds.
(Edited)
Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb
Hey Dan, i'm not sure if you're making some sort of joke but welcome to the party.  :)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
I am a pretty serious guy and would never joke around especially when dealing with mathematical topics or algorithmic folk dancers.

Photo of John Rogers

John Rogers

  • 340 Points 250 badge 2x thumb
I’m pretty swamped at work right now, but I’ll write the code out for you in awhile if you still need.
Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb
Wow that would be GREAT! Thank You!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
Here is a JavaScript function getMedian that takes (1) a table dbid(2) a query string, and (3) a fid of a numeric field and returns a promise that resolves to the median:

function getMedian(dbid, query, fid) {
  return Promise.resolve(
    $.get(dbid, {
      act: "API_DoQuery",
      query,
      clist: fid,
      fmt: "structured"
    })
  ).then(function(xml) {
    var data = $(`qdbapi table records record f[id=${fid}]`, xml).map(function() {
      return parseFloat($(this).text());
    });
    data.sort((x, y) => x - y);
    var ilm = Math.floor((data.length - 1) / 2);
    var ihm = Math.ceil((data.length - 1) / 2);
    var median = (data[ilm] + data[ihm]) / 2;
    return median;
  })
}

You call it like this:
var dbid = "bnxssrg9s";
var dbidTable = "bnzd357bm";
var apptoken = "cnurzq5cgchgjgdsdiup6cgkq6ct";
$.ajaxSetup({data: {apptoken}});
var query = "{3.GT.0}";
var fid = "8";
getMedian(dbidTable, query, fid)
  .then(function(median) {
    console.log(median);
  });

When called on a sample application with this numeric data:

0: 60000
1: 65000
2: 70000
3: 75000
4: 85000
5: 155000
6: 160000
7: 200000

it correctly calculates the median as 80000 (average of midpoints 75000 and 85000).

The organization of this script can calculate any mean, metric or norm of a list of numeric data.

I created this hypothetical GUI interface so QuickBase developers can hardcode the most popular means, metrics and norm functions:


(Edited)
Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb
Whoa!

Thank you Dan!   I'm going to try this now.
Photo of Stephen

Stephen

  • 134 Points 100 badge 2x thumb
Hey Dan! One more question for you, first your code RAN BEAUTIFULLY, but question how do I pass this value created by the javascript to a numeric field so that it can be reported on.