rank in script

[rajeshqvd]

Hello i am getting Count need Rank output thanks advance

ID	Color	Count	Rank
111	blue	4	4
111
111	blue	4	3
111
111
111	blue	4	2
111
111	blue	4	1
222
222	red	3	3
222	red	3	2
222	red	3	1
222

[avkeep01]

Hi @rajeshqvd

Basically you can start with: 

RANK(COUNT(Count))

The next step is using aggregates over color or ID, depending on what you need. 
For example: 

AGGR(RANK(COUNT(Count)),ID)

But based on the column count you won't get far because all the values are the same per ID. 

[rajeshqvd]

Need to build it in Script Not front end

[avkeep01 · Accepted solution]

Right, no problem. 

Sort you table based on your count and then add: 

IF(ROWNO()=1,1,PEEK('Rank',-1)+1) AS Rank

It peeks at the previous rows and adds 1 to that number. When sorted at the correct count field you'll get a Rank. 

If you want to start a new count for every ID use this: 

IF(PEEK('ID',-1)<>ID,1,PEEK('Rank',-1)+1) AS Rank

[steax]

brilliant thank you 🙂