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%

[#2]

From the data give above I would like to show it as

Date                01/01/2013          01/02/2013

Lonodn (rank)         6                         2

[jerem1234 · Accepted solution]

Used the formula:

only({<Location={'London'}>}aggr(nodistinct rank(sum(Perf)), Date, Location))

Please find attached. Let me know if this works for you.

Hope this helps!

[#4]

Thanks Jerem for your help.

[jerem1234]

You can actually remove nodistinct from this formula, you don't need it. I forgot to remove it when I was testing out different formulas.

only({<Location={'London'}>}aggr(rank(sum(Perf)), Date, Location))

[#6]

Yep. I did take it off. I was doing some trial and error too.