Formula for Record Ranking
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
Prim...
Forum Discussion
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
------------------------------
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
------------------------------
Elwyn,
thanks for posting this and showing hope.I tried your formula on are it's ID field with fieldid=3
i simply get numeral 1 as the rank in field instead of 1,2,3 etc
------------------------------
Prashant Maheshwari
------------------------------
