Rank function in expression

[#1]

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?

[#2]

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.

[whiteline]

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

[#4]

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]

aggr(nodistinct Avg(Value), Batch)

[#6]

Thanks Whiteline. That worked.

[#7]

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!

[#8]

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]

Thank you - this helped a lot!