Rank

[krish2459]

Hi ,

I need to derive the rank in the below format.

Highlighted column is the desired out put.

Row Labels	Sum of Weighted	Normal Rank	Required Rank
Falkland Islands	1	1	1
Montserrat	1	1	1
Anguilla	2.77815125	2	3
Cayman Islands	2.77815125	2	3
Dominica	2.77815125	2	3
Saint Kitts and Nevis	2.77815125	2	3
Saint-Martin	2.77815125	2	3
Grenada	3.77815125	3	8
Qatar	3.77815125	3	8
Saint Vincent and Grenadines	4.455773409	4	10
Caribbean Netherlands	4.556302501	5	11
United Arab Emirates	4.77815125	6	12
Aruba	6.430300856	7	13
Virgin Islands	7.023499423	8	14
Bahamas	7.23392466	9	15
Turks and Caicos Islands	9.478098186	10	16

[Kushal_Chawda]

@krish2459  try below expression. Replace highlighted by your actual sum of weight expression. You can remove the "-" sign if you want to rank highest weight as 1 and so on

=rank(total -sum([Sum of Weighted]),0,1)

[sunny_talwar]

@krish2459 This post I wrote sometime ago might come handy here

Continuous Ranking (no missing Rank) 

[Kushal_Chawda]

@krish2459  Updated my previous reply as don't need rounding

[krish2459]

Is it possible to achieve in load script.

[Kushal_Chawda]

@krish2459  Can you share sample data? Also Sum Of weight is actual field or calculated?

[sunny_talwar · Accepted solution]

@krish2459 try this

Table:
LOAD * INLINE [
    Row Labels, Sum of Weighted
    Falkland Islands, 1
    Montserrat, 1
    Anguilla, 2.77815125
    Cayman Islands, 2.77815125
    Dominica, 2.77815125
    Saint Kitts and Nevis, 2.77815125
    Saint-Martin, 2.77815125
    Grenada, 3.77815125
    Qatar, 3.77815125
    Saint Vincent and Grenadines, 4.455773409
    Caribbean Netherlands, 4.556302501
    United Arab Emirates, 4.77815125
    Aruba, 6.430300856
    Virgin Islands, 7.023499423
    Bahamas, 7.23392466
    Turks and Caicos Islands, 9.478098186
];

FinalTable:
LOAD *,
	 RangeSum(Peek('tmpRank'), 1) as tmpRank,
	 If([Sum of Weighted] = Previous([Sum of Weighted]), Peek('Required Rank'), RangeSum(Peek('tmpRank'), 1)) as [Required Rank]
Resident Table
Order By [Sum of Weighted];

DROP Table Table;
DROP Field tmpRank;

[krish2459]

Hi,

Here is the script.

Armed_Conflict_Temp:
LOAD
sub_event_type,

event_type,

interaction,
region,
trim(upper(country)) as Country,
trim(upper(country)) as Country4,
admin1,

fatalities

FROM [lib://ISC_Data (code1_320001959)/Projects/Secuity Analytics/Risk Management/Development/QVDs/Armed Conflict/Armed Conflict.qvd]
(qvd)where Len(trim(location))>0;

join

Load sub_event_type,Country,
if(sum(fatalities)>0,2,1) as EventWeight,
if(sum(fatalities)>0,2,1)*sum(interaction) as "Event Weightage"

Resident Armed_Conflict_Temp Group by sub_event_type,Country;

Attached is the sample data.

Thanks

[Kushal_Chawda]

@krish2459  What is expected output based on this data?

[krish2459]

to get similar logic from excel

"=RANK.EQ(J4,$J$4:$J$158,1)/COUNT($J$4:$J$158)"

Percentile of "Event Weightage" based on "Event Weightage" rank.