Need to calculate RANK

[#1]

Hi,

I have a table that looks something like this:

City	Numerator	Denominator	(Numerator/Denominator)*100
South London	7	12	58.30%
London	41	53	77.30%
East london	50	56	89.20%

I have the bar chart and it displays the percentage of each city

I need to calculate the RANK in a TEXT BOX for "LONDON"

It should look like

London - Rank - 2 in a TEXT BOX.

Please help me out. I am new and struggling with this.

[its_anandrjs]

Write a script in expression like

Rank( Sum( (Numerator/Denominator)*100 ), 0, 1 )

HTH

Regards,

Anand

[#3]

I created a text box and wrote the expression you mentioned. It just says 1 in the TEXT BOX.

[its_anandrjs]

In a text box write this

=only({<City={"=rank(sum({<City={'London'}>}(Numerator/Denominator)*100))=1"}>}

aggr(rank(sum((Numerator/Denominator)*100)),City))

HTH

Regards,

Anand

[Anonymous · Accepted solution]

Anand,

Nice solution.  Two suggestions:

1. I think the set could be simple {<City='London'>}

2. To avoid rank like '2 - 5'0 when there are multiple Cities with the same amount, use num(rank(...))

=only({<City={'London'}>} aggr(num(rank(sum((Numerator/Denominator)*100))),City))

Regards,

Michael

[#6]

Sorry Anand. It doesnt work.  Just gives  as output

[#7]

Sorry Anand. It doesnt work.  Just gives a minus sign in the text box when I apply it. 

[#8]

Thanks Anand and Michael. I made some Changes and it worked.

The changes I have made was to add the MOde to the expression.