Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Overall ranking within a table containing several dimensions

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

11 Replies
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

Not applicable
Author

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