rank for group

[alanwong1178]

Country	City	Sales	Rank for city	Rank for country
GB	LHR	501	1	3
GB	MAN	20	9	3
US	NYC	300	4	1
US	LAX	200	5	1
US	SFO	304	3	1
CN	SHA	100	8	4
CN	PEK	115	7	4
JP	TYO	500	2	2
JP	OSK	149	6	2

I would like to do two ranking column. One for rank for all city. Another rank is based on total of each country.

Please kindly help. Thanks.

Also, I would like to do a dynamic top N for country field. This means that if I set to get top 2, result can be shown as below.

Country	City	Sales	Rank for city	Rank for country
US	NYC	300	4	1
US	LAX	200	5	1
US	SFO	304	3	1
JP	TYO	500	2	2
JP	OSK	149	6	2

[GaryGiles]

You need to incorporate the aggr() function into your expressions.

For Rank for City, you could use the following in a dimension expression:

=aggr(Rank(sum({1} Sales)), City)

For Rank for Country, you could use the following in a dimension expression:

=if(aggr(Rank(sum({1} Sales)), Country)<=2,aggr(Rank(sum({1} Sales)), Country))

The if statement is for picking the dynamic top N for the Rank in Country.  To make it dynamic, you will need to replace the "2" with the variable you will use to set the Top N.  You will also need to uncheck "Include null values" for this expression.