Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Re: Rank function in expression

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
Honored Contributor II

Re: Rank function in expression

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

Not applicable

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.

whiteline
Honored Contributor II

Re: Rank function in expression

aggr(nodistinct Avg(Value), Batch)

Not applicable

Re: Rank function in expression

Thanks Whiteline. That worked.

Not applicable

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!

Not applicable

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

Highlighted
Partner
Partner

Re: Rank function in expression

Thank you - this helped a lot!