Ranking rows based on multiple criteria

[Avi Levy]

Hi All!

Happy new year. I have searched for a while before posting, but I still can't quite understand what I need, so any help is most appreciated.

 

I am trying to rank rows based on a list of criteria:

 

 

 

Study Number

TA

Sector

Survey

Name

ID

Rank

 

 

775

ONC

MA

Close Out

Mr A

204724

2

 

 

193

ONC

G

Conduct

Mr A

204724

3

 

 

47

ONC

G

Start Up

Mr A

204724

1

 

 

217

ONC

RED

Close Out

Mr B

210850

1

 

 

48

ONC

G

Close Out

Mr B

210850

2

 

 

324

ONC

G

Conduct

Mr B

210850

3

 

 

217

NS

G

Conduct

Mrs A

212509

1

 

 

324

ONC

G

Conduct

Mrs A

212509

2

 

 

112

ONC

RED

Close Out

Mrs B

237388

2

 

 

137

ONC

G

Start Up

Mrs B

237388

1

 

 

 

The idea is to get a ranking for each person, who is involved in multiple studies (Study Number column) within eachID (last column) - i.e. create a ranking of which survey to send out for each person when a given person is involved in multiple studies. The rules that need to be integrated into this are as follows (in order of importance):

Survey Column: Start-up takes first rank over everything else, followed by close out, and then conduct. See example for Mr A.

Sector Column: If there is a tie within the Survey column, the sector column is next. The order is RED first, then G, then MA. See example for Mr B.

TA column: If there is a tie from any of the above, NS in the TA column beats ONC.

 

I know a Case when might do this, but I am not so hot on knowing how to do this so any help please let me know!

 

Cheers,

Avi

[Kirsten Smith (she/her)]

So what happens when you do something like:

 

 

 

Rank([Name], "desc", [survey], [sector], [TA])

 

 

 

Since you're using Rank() on String columns you will need to go into Data/Column Properties and set the desired Sort Order, as that's what the Rank() will use.

[Avi Levy]

Hi Kristen! Thanks for the very quick and helpful reply. 

 

 

 

I managed to do what you said with ranking order in the column settings, and then swap round your formula, getting the following which (nearly) works:

 

Rank([survey],[Executing Party],[TA],"asc",[CTMS ID])

 

I.e for each "CTMS ID", that group of rows is ranked by the criteria mentioned above, where "start Up" takes priority etc. 

 

 

 

However, there seems to still be ties, and I would like to incorporate a new column called "Enrolled" as the final criteria which will decide between what is tied.  For example 

 

Enrolled	TA	Sector	Survey	Name	ID	Rank
2	ONC	G	Conduct	Mr A	204724	1
4	ONC	G	Conduct	Mr A	204724	1

 

 

 

However, this new "enrolled" column is type Real, and it doesn't seem to play ball when putting it in the formula above - all the ranks become 1. 

 

Therefore, do you know how I can incorporate this Real column, which is a number of something, into the ranking

 

 

 

Thanks!

 

 

[Kirsten Smith (she/her)]

You should be able to add "ties.method=first" to your Rank() function, and wrap your [Enrolled] with String() to negate the type mismatch