Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You need a TOTAL within your rank-expression.
- Marcus
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.
Try: rank(max(total <[Field A]> value))
- Marcus
Well,
This triggers an error in the expression.
Is it possible on your side to send a small application?
JR
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
Yes,
I confirm that the behavior is not the one expected.
Please refer to the attached qvw file.
Thanks,
JR
The reason is you have nested aggregations without an aggr-function, this should work:
rank(max(total <Location> aggr(Sum(Price), Location)))
- Marcus
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
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