Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)

t1.png

But I'm trying to get the following:

t2.png

Is it possible to gain above result?

17 Replies
Not applicable
Author

BatchSub BatchValueRankNorm_Rank
AA_60.3010.2
AA_80.3020.4
AA_90.7930.6
AA_50.8640.8
AA_70.9251
BB_50.2910.2
BB_10.4020.4
BB_20.4330.6
BB_40.4440.8
BB_30.9751
CB_60.2510.2
CB_70.3020.4
CB_20.7930.6
CB_30.8440.8
CB_40.9751

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.

whiteline
Master II
Master II

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

Not applicable
Author

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.

whiteline
Master II
Master II

aggr(nodistinct Avg(Value), Batch)

Not applicable
Author

Thanks Whiteline. That worked.

Not applicable
Author

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!

Not applicable
Author

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

jarno_loubser
Partner - Creator
Partner - Creator

Thank you - this helped a lot!