Skip to main content
Question

Formula for Record Ranking

  • September 17, 2019
  • 0 replies
  • 160 views
I am looking for advice on creating an automated numeric rank of records based on 2 fields. Here is an example of the data I'm working with and what the desired results are:

Record Primary Code Secondary Code Rank
Project A Medium Benefit $10,000 2
Project B High Benefit $20,000 1
Project C Low Benefit $5,000 3

Project B is ranked 1 because it is in the High Benefit Category and it's Secondary Code value is higher than Project A.

If I were to add a Project D with Primary Code=High Benefit and Secondary Code=$50,000, then Project D would be ranked 1 and the rest of the records would be shifted down one rank. 

Appreciate any suggestions or advice!

------------------------------
Courtney Rapp
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
You can do this on a Summary report but no (or not easily) as actual field...  make a formula numeric field with a formula value of 1, 
called [Rank].

Make summary report and group it how you like, but ensure that every record is on its own line,.

for example group by Primary code, and then by Secondary code and then by project name.  Include the [rank] field on the report but show as a running total down the column.

The sort will probably work correctly if the Primary code if form a simple multiple cice field set to sort n the correct order.  Or else you will need to introduce a new formula field or lookup field off the benefit categories table to force the correct Alpha sort, such as 

1. High Benefit
2. Meduim Benefit
3. Low Benefit.


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
  • ElwynDow1
  • Registered
  • September 27, 2021
This formula will rank records based on a numeric field - uses the new Query category of Functions

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