How to order value when a rank is used ?

[#1]

Good day,

I have the following data:

I want to display for each "agence", the top 2 "rubrique" based on the "sum(Note)" expression.

When I use the "rank" function, I don't succed to make the sort I want on the "rubrique" field.

Do you have any idea why?

Do you know how I can display what I want?

[derekjones]

Hi Nicolas

How about adding a new hidden expression with rank and sort by that ascending using a straight table. See your example modified attached.

Derek

[#3]

How can I do it with a cross table?

[derekjones]

I thought you might ask that.

Pivots are a nightmare to sort by the value. The only way I could see to do this is by adding a 'hidden' (well almost) dimension which has rank to be able to sort your data.

See attached.

[derekjones]

Actually, thinking about it, there's a much more elegant solution. Use the dual function on Dimension Rubrique, which has a numeric vaue of the rank. That way it can sort on this field.

See attached.

[#6]

In your exemple, the values are good, but the "rubrique" displayed are false.

It shows:

BBB     R4    5

             R3    3

Whereas I expect.

BBB     R3    5

            R5    3

[derekjones]

Fair point, then the attached is then the way to go, using an extra 'hidden' dimension.

KR

Derek