Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
From the data give above I would like to show it as
Date 01/01/2013 01/02/2013
Lonodn (rank) 6 2
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!
Thanks Jerem for your help.
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))
Yep. I did take it off. I was doing some trial and error too.