Implementing a stack rank function?
I need to implement a stack rank of a list of items in a quickbase table. Is there a way to write a formula to update all records stack ranks when the value is set for a new item?
Forum Discussion
- What is a "stack rank"? Can you give an example with specific values before and after the update?
- Basically I want to keep all records in a table ordered, so if i had 10 items, they would be numbered 1, 2, 3, ...10. If user inserts a new record and assigns it as 5, then existing 5, 6, 7,... 10 would all get incremented by 1.
- You posted the question about numbering sorting and number records in a table a number of years ago. Did you ever get a response or figure out how to do it? I have been looking for a formula to build ranking on reports with no luck.
- Summary reports can essentially do ranking because if you do a summary report but basically break on every record, you can have a field with a formula value of 1 and display that as running totals down the page. So they self number from 1 to .. whatever.
- Hey so I am trying to rank based off of a calculation. My calculation returns a value (i.e. 100,115, 50,000, 10,000, 2103) I want those values ranked 1 through 100 based Highest to lowest. For example, 100,115 would equal 1 and 2103 equals 100, if there were 100 records.
- See the answer directly above your post.
"Summary reports can essentially do ranking because if you do a summary report but basically break on every record, you can have a field with a formula value of 1 and display that as running totals down the page. So they self number from 1 to .. whatever. "
I believe that is the only solution, but I know if does not actually create a field on the record. - I need a field that can be used on multiple reports
- I will try that, but I think that will take up a lot of room on the screen, and certainly not as compact as being able to identify a field on any record as a placeholder or rank. It just surprises me that as Quickbase already counts the number of records in a report (displays the number of records in the report) there isn't a checkbox or something on report set up that allows for row numbering.
- Did you come up with a solution in the end?
------------------------------
Nick Charalambous
nick@em-dev.co.uk
London, UK
------------------------------- 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
------------------------------
