Rank in table

[#1]

1. How do I get a Rank for 'London' in a Straight table or Pivot table with dates as dimensions.

2. Display the Average rank of 'London' in a straight or pivot table with dimension as dates

I managed to get rank to display in the text box by using the code: Only({<Location={'London'}>} Aggr(num(rank(sum(Perf),1)),Location))

data in the form of (see below)

Location	Date	Perf
London	01/01/2013	60%
Birm	01/01/2013	62%
Cardiff	01/01/2013	34%
new York	01/01/2013	12%
Tokyo	01/01/2013	63%
Calif	01/01/2013	84%
Nairobi	01/01/2013	12%
Calcutta	01/01/2013	58%
Sydney	01/01/2013	73%
Melbourne	01/01/2013	63%
London	01/02/2013	99.3%
Birm	01/02/2013	94.2%
Sydney	01/02/2013	95.7%
Melbourne	01/02/2013	87.1%
Calif	01/02/2013	92.7%
Nairobi	01/02/2013	100.0%
Calcutta	01/02/2013	96.9%
Cardiff	01/02/2013	98.3%
new York	01/02/2013	93.5%
Tokyo	01/02/2013	91.8%

[MK_QSL]

Can you clarify what you want to get ?

Rank only for LONDON compare to different dates?

[MK_QSL]

AGGR(RANK(SUM({$<Location = {'London'}>}Perf)),Location)

[#4]

Yes please. Rank of Only London on different dates in a table.

The above code gives the rank as '1'

[MK_QSL]

Try This...in straight table...

Dimension = Date

Expression = as below...

AGGR(RANK(SUM({$<Location = {'London'}>}Perf)),Date)

[#6]

Sorry Manish. I get Rank as 1 and Ranks as 2 for above dates

[MK_QSL]

Can you load an example of your result requirements?

i.e. How you want your result table...

[#8]

Yes, it will give rank 1 and 2. What do you expect out of it. Could you create a dummy chart and help us know what exactly you want? koushik vutha

[#9]

Data I have provided above is an example of how the data is structured. I would like to show the Rank of London when compared to other cities and display only rank of London in the table. Example below.

Date	07/04/2013	14/04/2013	21/04/2013
London (Rank)	51	16	8

[#10]

Hi Deepak

From above Rank of London for the dates 01/01/2013 is 6 and for 01/02/2013 is 2.