Get rank in textbox

[woshua5550]

Hello experts

I need a expression in a textbox which will show current selected city's rank

plz have a look at the sample data below

City	Amount
Beijing	100
Shanghai	500
Beijing	200
Chongqing	150

when I select "Beijing" it should return "2" , when I select "Shanghai" it should return "1" ,"Chongqing" is "3"

I don't  want to deal with it during data loading,plz give me the frontend solution

thanks

[tresesco · Accepted solution]

Try like:

=Aggr(rank(Sum({<City>}Amount)),City)

[woshua5550]

Thanks Bro! It works

but how should I change this expression if I use alternate states on city filter ?

let's say I have city in a list box with Alternate States "G1" and  textbox with default states

I tried "=Aggr(rank(Sum({G1<City>}Amount)),City)" but it doesn't work

plz help , thank you

[pooja_prabhu_n]

Try like this in text box

=aggr(rank(sum( {1<city=>}  Amount)),city)

[sunny_talwar]

Try this may be

=Only({G1} Aggr(Rank(Sum({<City>} Amount)), City))

[woshua5550]

Excellent ! Thank you Sunny!