Overall ranking within a table containing several dimensions

[#1]

Hello all,

I have a pivot table with 2 dimensions (Field A is horizontal, Field B is vertical), and I need to get the global ranking through those 2 dimensions. For instance, I want to get the overall max value of this 2 dimensions table, the 2nd max value, 3rd... and so on.

You can refer to the attached xls.

Until now, with the rank() and hrank() functions, I only succedeed to compute the ranking per column or per row.

Do you have any idea to compute this?

Thanks,

JR

[marcus_sommer]

You need a TOTAL  within your rank-expression.

- Marcus

[#3]

Hello Marcus,

Thanks for your answer.

Infortunately, I had already tried the TOTAL, but it does not work actually.

In this particular case, it gives me the same result than without the total.

[marcus_sommer]

Try: rank(max(total <[Field A]> value))

- Marcus

[#5]

Well,

This triggers an error in the expression.

Is it possible on your side to send a small application?

JR

[marcus_sommer]

You could here post a small example but I think the error is only because you had used this expression without adaption to your real fieldnames. My suggestion is a generic one.

- Marcus

[#7]

Yes,

I confirm that the behavior is not the one expected.

Please refer to the attached qvw file.

Thanks,

JR

[marcus_sommer]

The reason is you have nested aggregations without an aggr-function, this should work:

rank(max(total <Location> aggr(Sum(Price), Location)))

- Marcus

[#9]

Thanks Marcus.

But it is still not matching my needs.

This is I get from the expression:

Actually, with this set of data I would like to get:

241 --> 1

165 --> 2

155 --> 3

...

The idea is to obtain the overall ranking through all rows and columns.

Thanks,

JR

[ramoncova06]

I am afraid what you are looking cannot be achieved, since the Rank function is based at column level and HRank is at row level