Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
polisetti
Contributor

Sorting the expression on 2 metrics

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.

RankLivesMarket Share
150020%
2

400

40%
320050%
410080

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

4 Replies
MVP
MVP

Re: Sorting the expression on 2 metrics

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

MVP
MVP

Re: Sorting the expression on 2 metrics

rank(Lives - [Market Share]/1000)

1.png

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;

polisetti
Contributor

Re: Sorting the expression on 2 metrics

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.

MVP
MVP

Re: Sorting the expression on 2 metrics

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.

Community Browser