Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I see the rank by Lives. But what do you mean with less(Market Share)? Could you describe this a bit more?
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;
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.
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.