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]

I think RamonCova06  is right that it couldn't be achieved with the rank-function because in this case vertical and horizontal dimensions are mixed up. Therefore the "normal" suggestion would be to use an object with only one kind of dimension-orientation.

But if you could work with an "ugly" workaround like the following you could keep your object-structure:

substringcount($(=chr(39) & concat(distinct total aggr(NODISTINCT sum(Price), Location, [Product type]), ',',

                                aggr(NODISTINCT sum(Price), Location, [Product type])) & chr(39)), ',') -

substringcount(                               

Left($(=chr(39) & concat(distinct total aggr(NODISTINCT sum(Price), Location, [Product type]), ',',

                                aggr(NODISTINCT sum(Price), Location, [Product type])) & chr(39)),

Index($(=chr(39) & concat(distinct total aggr(NODISTINCT sum(Price), Location, [Product type]), ',',

                                aggr(NODISTINCT sum(Price), Location, [Product type])) & chr(39)),

      sum(Price))-1), ',') + 1

Maybe extended with additionally options for dimensionality() and/or seconddimensionality() queried per if-loop. Whereby every step will increase the complexity and might be overall with real data (too) slow.

- Marcus

[#3]

Hello,

Thank you very much for your answers and help.

I do not wish to go for the dirty workaround, so I will find on my side other solution.

Thanks again!

JR