17 Replies Latest reply: Aug 27, 2015 8:42 AM by Jarno Loubser

# Rank function in expression

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

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

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

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

Thanks, it works.

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

• ###### Re: Rank function in expression

Yes, I think you can.

The menthod is just 'calculated dimension'.

• ###### Re: Rank function in expression

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

=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

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

And what is the problem with the expressions provided above ?

• ###### Re: Rank function in expression
 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

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

• ###### Re: Re: Rank function in expression

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

aggr(nodistinct Avg(Value), Batch)

• ###### Re: Rank function in expression

Thanks Whiteline. That worked.

• ###### Re: Rank function in expression

Thank you - this helped a lot!

• ###### Re: Rank function in expression

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

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