topic Re: Overall ranking within a table containing several dimensions in QlikView

Overall ranking within a table containing several dimensions

Wed, 29 Apr 2015 10:06:13 GMT

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,

Re: Overall ranking within a table containing several dimensions

marcus_sommer — Wed, 29 Apr 2015 10:12:47 GMT

You need a TOTAL within your rank-expression.

- Marcus

Re: Overall ranking within a table containing several dimensions

Wed, 29 Apr 2015 10:59:34 GMT

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.

Re: Overall ranking within a table containing several dimensions

marcus_sommer — Wed, 29 Apr 2015 11:07:35 GMT

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

- Marcus

Re: Overall ranking within a table containing several dimensions

Wed, 29 Apr 2015 11:28:51 GMT

Well,

This triggers an error in the expression.

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

Re: Overall ranking within a table containing several dimensions

marcus_sommer — Wed, 29 Apr 2015 11:36:02 GMT

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

Re: Overall ranking within a table containing several dimensions

Wed, 29 Apr 2015 14:14:13 GMT

Yes,

I confirm that the behavior is not the one expected.

Please refer to the attached qvw file.

Thanks,

Re: Overall ranking within a table containing several dimensions

marcus_sommer — Wed, 29 Apr 2015 14:27:24 GMT

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

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

- Marcus

Re: Overall ranking within a table containing several dimensions

Wed, 29 Apr 2015 14:54:49 GMT

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,

Re: Overall ranking within a table containing several dimensions

ramoncova06 — Wed, 29 Apr 2015 21:36:11 GMT

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

Re: Overall ranking within a table containing several dimensions

marcus_sommer — Thu, 30 Apr 2015 08:19:04 GMT

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

Re: Overall ranking within a table containing several dimensions

Thu, 30 Apr 2015 17:20:04 GMT

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!