topic Re: Rank function for repeated data in QlikView

Rank function for repeated data

marjan_it — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have a problem in Rank function.

my data i like this :

code	Number	Name
1712	51.11	A
1306	50.00	B
504	50.00	C
1702	48.10	D
1206	47.83	E
1801	47.37	F
505	46.88	G
1517	46.43	H
1212	46.39	I
521	43.75	J

I want to show top 10 Name in text object in sort I use this

=Aggr(if( rank(sum(Number))=1 ,Name),Code)

It is work for non repeated data but for repeated data its not work. what should I do to show data.

qvw file is attached .

Re: Rank function for repeated data

Anil_Babu_Samineni — Sun, 09 Oct 2016 07:15:39 GMT

Is This you are expecting

Re: Rank function for repeated data

marjan_it — Sun, 09 Oct 2016 07:17:38 GMT

No, In my qvw file i explain what I expect

Re: Rank function for repeated data

Anil_Babu_Samineni — Sun, 09 Oct 2016 07:34:44 GMT

I don't have license. For this what are you expectingg

Re: Rank function for repeated data

marjan_it — Sun, 09 Oct 2016 07:41:05 GMT

This is what show and what I need.

I use rank for 1 to 10 in text object but in 2 and 3 its not show.

Re: Rank function for repeated data

Anil_Babu_Samineni — Sun, 09 Oct 2016 07:47:17 GMT

Might be this

RowNo(total) - rangesum(above(total if(num(rank(total Number,1))=above(num(rank(total Number,1))),1,0),

0,RowNo(total)))

Re: Rank function for repeated data

marjan_it — Sun, 09 Oct 2016 08:05:29 GMT

hmm, not actually.

I want to Show order of rank name in text object not in chart.

for example I want to show the 2nd number name in thext object. means show B or C.

When I use this [ Aggr(if( rank(sum(Number))=2 ,Name),Code) ] in text onject is show -.

Re: Rank function for repeated data

marcus_sommer — Sun, 09 Oct 2016 08:23:23 GMT

Maybe this is helpful: Continuous Ranking (no missing Rank)

- Marcus

Re: Rank function for repeated data

marjan_it — Sun, 09 Oct 2016 10:37:20 GMT

I found It, the correct answer is that use :

=Aggr(if( rank(sum(Number),4,0)=2 ,Name),Code)

Re: Rank function for repeated data

sunny_talwar — Sun, 09 Oct 2016 12:54:04 GMT

Just to add another set of possibility...

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code))

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code), 2)

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code), 3)

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code), 4)

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code), 5)

=FirstSortedValue(Name, -Aggr(Sum(Number)*100000 - Ord(Name), Code), 6)