topic Re: Sorting the expression on 2 metrics in QlikView

Sorting the expression on 2 metrics

polisetti — Tue, 14 Jul 2015 17:50:44 GMT

Hi,

Hope all are doing good!

My requirement is, I need to the rank the dimension on two metrics in my straight table. Consider there are two metrics in the table and the metrics are Lives and Market Share. So I need to rank the dimension and the first Rank should be calculated as Max(Lives) + less(Market Share) will be Rank 1. Please refer below example.

Rank	Lives	Market Share
1	500	20%
2	400	40%
3	200	50%
4	100	80

So the rank 1 should be max(Lives) and less(Market Share).

Can we have any way to write the rank expression in the above way in QlikView?

Thanks,

Polisetti

Re: Sorting the expression on 2 metrics

swuehl — Tue, 14 Jul 2015 17:55:04 GMT

I see the rank by Lives. But what do you mean with less(Market Share)? Could you describe this a bit more?

Re: Sorting the expression on 2 metrics

maxgro — Tue, 14 Jul 2015 18:05:58 GMT

rank(Lives - [Market Share]/1000)

source:

LOAD

rowno() as id,

Rank,

Lives,

replace([Market Share], '%', '')/100 as [Market Share]

FROM

[https://community.qlik.com/thread/172554]

(html, codepage is 1252, embedded labels, table is @1);

// add some test data

Concatenate (source)

load

RowNo() as id,

Rank,

Lives,

[Market Share] - 0.1 as [Market Share]

Resident source;

Concatenate (source)

load

RowNo() as id,

Rank,

Lives,

[Market Share] + 0.1 as [Market Share]

Resident source;

Re: Sorting the expression on 2 metrics

polisetti — Tue, 14 Jul 2015 18:09:04 GMT

I have a dimension as State Name and metrics as Lives and Market Share. Market Share is (Sales for company product/ Sales of All Products). So now I need to rank the State Name in a ascending order by max (lives) and then lowest (market share).

Please let me know if I need to give you a detailed example.

Thanks for the quick response.

Re: Sorting the expression on 2 metrics

swuehl — Tue, 14 Jul 2015 19:03:51 GMT

As I understand you want to use Market Share to break ties when ranking on Lives.

I was just a little confused because your sample data hasn't showed any example on tie breaking.

Something like Massimo suggested should work then.