Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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%
1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Rank in table

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!

14 Replies
MVP
MVP

Re: Rank in table

Can you clarify what you want to get ?

Rank only for LONDON compare to different dates?

MVP
MVP

Re: Rank in table

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

Not applicable

Re: Rank in table

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

The above code gives the rank as '1'

MVP
MVP

Re: Rank in table

Try This...in straight table...

Dimension = Date

Expression = as below...

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

Not applicable

Re: Rank in table

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

MVP
MVP

Re: Rank in table

Can you load an example of your result requirements?

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

Not applicable

Re: Rank in table

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

Not applicable

Re: Rank in table

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.

Date07/04/201314/04/201321/04/2013
London (Rank)51168
Not applicable

Re: Rank in table

Hi Deepak

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

Community Browser