Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank in table

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)

LocationDatePerf
London01/01/201360%
Birm01/01/201362%
Cardiff01/01/201334%
new York01/01/201312%
Tokyo01/01/201363%
Calif01/01/201384%
Nairobi01/01/201312%
Calcutta01/01/201358%
Sydney01/01/201373%
Melbourne01/01/201363%
London01/02/201399.3%
Birm01/02/201394.2%
Sydney01/02/201395.7%
Melbourne01/02/201387.1%
Calif01/02/201392.7%
Nairobi01/02/2013100.0%
Calcutta01/02/201396.9%
Cardiff01/02/201398.3%
new York01/02/201393.5%
Tokyo01/02/201391.8%
14 Replies
Not applicable
Author

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

Date                01/01/2013          01/02/2013

Lonodn (rank)         6                         2

jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

Thanks Jerem for your help.

jerem1234
Specialist II
Specialist II

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))

Not applicable
Author

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