Skip to main content
Question

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

  • October 21, 2016
  • 0 replies
  • 212 views

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.

This topic has been closed for replies.

  • anomDiebolt
  • Registered
  • October 21, 2016
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.
  • MichaelCanady
  • Author
  • Registered
  • October 24, 2016
Thanks Dan! I just sent you an email.
  • ElwynDow1
  • Registered
  • September 27, 2021
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
------------------------------
Terms & ConditionsAccessibility statement