Forum Discussion

MichaelCanady's avatar
MichaelCanady
Qrew Member
9 years ago

Is there a way to rank values in quickbase similar to Excel's RANK function?

I need help ranking values from largest to smallest in Quickbase. In the past, I have used Excel's RANK function to achieve this, however I am coming up empty trying to mimic this function in Quickbase. A simple sort on the field will not work. I am analyzing data broken down by State and Regions, so I have multiple fields that I want to be able to see the ranking for, so that I can compare those rankings side by side without having to sort by each column. Thanks in advance for any help.

Formulas and functions
reports and charts
Tables and fields
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    9 years ago
    You have to use script to do this. Feel free to contact me off-world using the information in my profile if you need personalized assistance to implement this.
    • RateSpy's avatar
      RateSpy
      Qrew Member
      8 years ago
      Hi Dan, I wasn't able to find your email. Would you be able to email me at info@ratespy.com? I have a similar need (to rank records) and need to hire someone to help. Thanks!
  • ElwynDow1's avatar
    ElwynDow1
    Qrew Trainee
    4 years ago
    Ranking formula using the new Query functions. Will rank records based on numeric field.  Ranks top 10, top 100, etc – or every value…

    var bool descOrder = true;            // true if ranking from high to low, otherwise false
    var number maxrank = 0;             // 0 to rank every number, else 10 to rank 1 through 10, 100 to rank 1 through 100, etc
    var number fieldId = 17;              // the field id of the numeric field being ranked - CHANGE AS NEEDED
    var number rankThis = [number field];  // the numeric field being ranked - CHANGE AS NEEDED
    var number rank    = If($descOrder, Size(GetCellValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
                                        Size(GetCellValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);
    If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))

    ------------------------------
    Elwyn Dow
    ------------------------------
    • MichaelCronin's avatar
      MichaelCronin
      Qrew Member
      4 years ago
      Hi Elwyn,

      Thanks for posting your solution.

      I have tried to use the code you provided above and I am getting the following error:

      There are one or more problems with your entry
      These function names are unknown.

      I've tried adding them using the "choose fields & functions", but still get an error.

      Any ideas what I need to check to get it working?

      var bool descOrder = true;               // true if ranking from high to low, otherwise false
var number maxrank = 0;                  // 0 to rank every number, else 10 to rank 1 through 10, 100 to rank 1 through 100, etc
var number fieldId = 44;                 // the field id of the numeric field being ranked - CHANGE AS NEEDED
var number rankThis = [Total kWh/sqm];   // the numeric field being ranked - CHANGE AS NEEDED

var number rank = If($descOrder, Size(GetCellValues(GetRecords("{"&$fieldId&".GT."&$rankThis&"}"),$fieldId))+1,
                                 Size(GetCellValues(GetRecords("{"&$fieldId&".LT."&$rankThis&"}"),$fieldId))+1);

If($maxrank=0,$rank, If($rank<=$maxrank,$rank,null))​


      ------------------------------
      Michael Cronin
      ------------------------------