topic Re: Rank function in expression in QlikView

Rank function in expression

Wed, 05 Sep 2012 13:02:03 GMT

Hi,

Anybody know how I should change the Rank formula using to get right result for the same values.

I'm using the following as a dimension- Aggr(Rank(Sum(Value),1,1),ID)

But I'm trying to get the following:

Is it possible to gain above result?

Re: Rank function in expression

whiteline — Wed, 05 Sep 2012 18:47:04 GMT

I've found this workaround:

Use your rank expression (=Aggr(Rank(Sum(Value),1,1),ID)) as a Sort expresion for ID dimension.

Delete your calculated dimension.

Add expression =rowno() and lable it 'Rank'.

Seems exactly as you want.

Re: Rank function in expression

Mon, 10 Sep 2012 11:55:40 GMT

Thank you for solution, but it is not working in my case.

I need show only top 30 client by revenue amount. There some problem with right ordering.

Re: Rank function in expression

whiteline — Mon, 10 Sep 2012 12:07:23 GMT

As I said use my expression for sort order:

=Aggr(Rank(Sum([Revenue (KSEK)]),1,1),Client)

and this as dimension instead of Client(to limit first 30):

=aggr(if(Rank(total Sum([Revenue (KSEK)]))<=30, Client), Client)

and check 'Suppress When Values Is Null'.

Re: Rank function in expression

Tue, 11 Sep 2012 07:16:11 GMT

Thanks, it works.

But if I want to do the same with Chart can I use the same method?

Re: Rank function in expression

whiteline — Tue, 11 Sep 2012 07:18:18 GMT

Yes, I think you can.

The menthod is just 'calculated dimension'.

Re: Rank function in expression

Tue, 15 Apr 2014 00:43:58 GMT

I need to keep the rank as a dimension. Actually, I need rank as a normalized value between 0 and 1.

How to achieve this?

Re: Rank function in expression

whiteline — Tue, 15 Apr 2014 10:31:57 GMT

=Aggr(Rank(Sum([Revenue (KSEK)]),1,1),Client)/Count(distinct Client)

=aggr(if(Rank(total Sum([Revenue (KSEK)]))<=30, Client), Client)/30

Re: Rank function in expression

Tue, 15 Apr 2014 20:09:46 GMT

Whiteline,

Please refer to the original post. I have the same issue. ID has duplicate values, but I still need unique ranks as part of the dimension.

Re: Rank function in expression

whiteline — Wed, 16 Apr 2014 06:23:17 GMT

And what is the problem with the expressions provided above ?

Re: Rank function in expression

Wed, 16 Apr 2014 16:42:21 GMT

Batch	Sub Batch	Value	Rank	Norm_Rank
A	A_6	0.30	1	0.2
A	A_8	0.30	2	0.4
A	A_9	0.79	3	0.6
A	A_5	0.86	4	0.8
A	A_7	0.92	5	1
B	B_5	0.29	1	0.2
B	B_1	0.40	2	0.4
B	B_2	0.43	3	0.6
B	B_4	0.44	4	0.8
B	B_3	0.97	5	1
C	B_6	0.25	1	0.2
C	B_7	0.30	2	0.4
C	B_2	0.79	3	0.6
C	B_3	0.84	4	0.8
C	B_4	0.97	5	1

This is the table that I have. What I am trying to do is have a trellis by Batch and create Rank on the fly and use Norm_Rank as the dimension.

=aggr(rank(sum(Value),1,1),Batch) gives me duplicate values in Rank

=aggr(rank(sum(Value),1,1),[Sub Batch]) gives me rank of the total population (1 to 15)

Can you help me get the rank as shown in the table.

Re: Rank function in expression

whiteline — Wed, 16 Apr 2014 18:07:58 GMT

Guessing that Batch and Sub Batch are dimensions the expression should be rank(Sum (Value)).

Re: Re: Rank function in expression

Wed, 16 Apr 2014 19:51:49 GMT

I have attached the sample qvw. I was able to get the calculated dimension working using the below

=aggr(Rank(sum(Value),4),Batch,[Sub Batch])

I have few other issues now

1) I want to show the avg of values as a line for each Batch separately.

Avg(Total <Batch> Value) - This expression gives the same number as Value instead of gettting the average of all the values per Batch.

2) I want to show the (avg + std dev) of values as a line for each Batch
avg(total<Batch> Value)+stdev(total<Batch> Value) - This expression gives a single number for all the values across the batches.

I am stumped.

Re: Rank function in expression

whiteline — Wed, 16 Apr 2014 20:29:07 GMT

aggr(nodistinct Avg(Value), Batch)

Re: Rank function in expression

Thu, 17 Apr 2014 18:48:13 GMT

Thanks Whiteline. That worked.

Re: Rank function in expression

Sat, 20 Sep 2014 11:58:46 GMT

Hi there! I tried this within my model, and it shows always from rank #2 and below, but #1 is never displayed. Any clue? I assumed because the nulls, but when apply nulls, they don't have the #1.

Thanks!

Re: Rank function in expression

Wed, 29 Jul 2015 07:20:07 GMT

Hi,

I am new to qlikview. To use rank function do we need to use sum in rank function, can't we use rank function directly to get top 5 records

Re: Rank function in expression

jarno_loubser — Thu, 27 Aug 2015 12:42:18 GMT

Thank you - this helped a lot!