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 Quickba...
Formulas and functions
reports and charts
Tables and fields
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
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Michael
    Formula Queries is in Open Beta but you need to request access.  They are carefully opening up access to be able to assess any performance issues.  There is a link near the end of this page for the September 2021 Release Notes.

    https://help.quickbase.com/release-notes/september-2021-release-notes.html

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------