# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
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)

 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%
1 Solution

Accepted Solutions
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

## Re: Rank in table

Can you clarify what you want to get ?

Rank only for LONDON compare to different dates?

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

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

## Re: Rank in table

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.

 Date 07/04/2013 14/04/2013 21/04/2013 London (Rank) 51 16 8
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.