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?

[whiteline]

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.

[#3]

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.

[whiteline · Accepted solution]

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'.

[#5]

Thanks, it works.

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

[whiteline]

Yes, I think you can.

The menthod is just 'calculated dimension'.

[#7]

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?

[whiteline]

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

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

[#9]

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.

[whiteline]

And what is the problem with the expressions provided above ?