topic Re: Rank in table in QlikView

Rank in table

Tue, 04 Feb 2014 12:44:47 GMT

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%

Re: Rank in table

MK_QSL — Tue, 04 Feb 2014 12:53:05 GMT

Can you clarify what you want to get ?

Rank only for LONDON compare to different dates?

Re: Rank in table

MK_QSL — Tue, 04 Feb 2014 12:53:35 GMT

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

Re: Rank in table

Tue, 04 Feb 2014 13:10:06 GMT

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

The above code gives the rank as '1'

Re: Rank in table

MK_QSL — Tue, 04 Feb 2014 13:18:43 GMT

Try This...in straight table...

Dimension = Date

Expression = as below...

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

Re: Rank in table

Tue, 04 Feb 2014 13:28:04 GMT

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

Re: Rank in table

MK_QSL — Tue, 04 Feb 2014 13:32:43 GMT

Can you load an example of your result requirements?

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

Re: Rank in table

Tue, 04 Feb 2014 13:36:18 GMT

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

Re: Rank in table

Tue, 04 Feb 2014 13:40:29 GMT

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

Re: Rank in table

Tue, 04 Feb 2014 13:44:31 GMT

Hi Deepak

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

Re: Rank in table

Tue, 04 Feb 2014 13:45:45 GMT

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

Date 01/01/2013 01/02/2013

Lonodn (rank) 6 2

Re: Rank in table

jerem1234 — Tue, 04 Feb 2014 13:59:22 GMT

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!

Re: Rank in table

Tue, 04 Feb 2014 14:02:10 GMT

Thanks Jerem for your help.

Re: Rank in table

jerem1234 — Tue, 04 Feb 2014 14:49:22 GMT

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

Re: Rank in table

Tue, 04 Feb 2014 14:53:46 GMT

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