topic Re: Ranking in case of tie breaker with respect to other metrics in QlikView

Ranking in case of tie breaker with respect to other metrics

mayankraoka — Mon, 16 Jan 2017 10:48:09 GMT

Hi All,

I am finding top 10 customers on the basis of their volume.The top 10 customers list ,I am deriving from calculated dimension.

If the ranking of the customer is same then need to check other metrics for example their profit.Is there any way i can check other metrics in case of tie in block chart?The below formula gives me top 10 customer based on volume units .

But in case of tie need to chekc other kpi and then give me top 10 customers list.

aggr(if(num(rank((sum(vol_units)),4,1))<=10,CUSTOMER),CUSTOMER)

Is this possible in block chart?Sorting option is not possible in block chart.

Any help is highly appreciated.

Regards,

Mayank

Re: Ranking in case of tie breaker with respect to other metrics

girirajsinh — Mon, 16 Jan 2017 11:03:46 GMT

Hi Mayank

I see you have used '4' as a mode for the rank function. I understand that it should not give you same rank for two rows as it gives distinct rank based on sort order of Dimension. So basically if you are showing two dimensions then it should give you what you are looking for . I might have misunderstood something here .

Could you share qvw with sample data ?

Giri

Re: Ranking in case of tie breaker with respect to other metrics

mayankraoka — Mon, 16 Jan 2017 11:07:12 GMT

yes you are correct initially we are using 4 as mode for Lowest rank on first row, then incremented by one for each row.

But now we want to check tie breaker as well for this we are using mode 3 highest value and now ranking are same.

we are using only one dimension that is customers on block chart.

Regards,

Mayank

Re: Ranking in case of tie breaker with respect to other metrics

MK_QSL — Mon, 16 Jan 2017 11:15:05 GMT

Use something like below

=aggr(if(num(rank((sum(vol_units)+sum(YourSecondMatrics)/1E10),4,1))<=4,CUSTOMER),CUSTOMER)

Re: Ranking in case of tie breaker with respect to other metrics

mayankraoka — Mon, 16 Jan 2017 11:33:20 GMT

Hi Manish,

Thanks for your reply .

I will try this but what is the logic behind the sum(YourSecondMatrics)/1E10),and /1E10 is it dummy ?

Regards,

Mayank

Re: Ranking in case of tie breaker with respect to other metrics

MK_QSL — Mon, 16 Jan 2017 11:42:39 GMT

It will divide your 2nd dimension total by 1000000000.

Re: Ranking in case of tie breaker with respect to other metrics

mayankraoka — Mon, 16 Jan 2017 13:43:11 GMT

excellent Manish.

Your logic working exactly the way I want.

Thank you very much.

Regards,

Mayank