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
5 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
5 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
------------------------------