topic Re: Rank on multiple dimension in QlikView

Rank on multiple dimension

ktembharey — Wed, 12 Apr 2017 08:23:01 GMT

Hi All,

I have a straight table which ranks Top 10 products based on their sales, for products with equal sales I get same rank. When the ranks are same I want to rank those products based on their market sales.

I have the below table,

For example , Products P1,P2 and P5 have same rank 9, now I want to rank them 7,8 and 9 based on their market volume.

I am using the below expression,

If(Aggr(Num(Rank(Sum (Volume))),Product) = Aggr(Num(Rank(Sum (Volume))),Product),

Aggr(Num(Rank(Sum (Volume),4,1)),Market,Product),

Aggr(Num(Rank(Sum (Volume),4,1)),Product))

Which gives me the below result,

As you can see here, I get discontinuous rank.

Here the expression ranks the products based on their market volume but again starts the ranking from 1.

I want to keep my interactive sort active so as the user can understand how the sorting is taking place here and need a continuous ranking on the products.

Can someone suggest a solution on how can I achieve this scenario?

Regards,

Krutika

Re: Rank on multiple dimension

sunny_talwar — Wed, 12 Apr 2017 14:01:59 GMT

Would you be able to share a sample qvw to take a look at?

Re: Rank on multiple dimension

kevinchevrier — Wed, 12 Apr 2017 14:27:11 GMT

Maybe you should try something like :

=Aggr(Num(Rank(TOTAL Sum (Volume),4)),Product,Market.Product)

Re: Rank on multiple dimension

ktembharey — Thu, 13 Apr 2017 14:51:32 GMT

Hi Kevin,

The "Market.Product" is not working in the expression. It gave me no values. I couldn't understand exactly how is this expression working though.

Later I found a solution for this scenario in another discussion Ranking in case of tie breaker with respect to other metrics which worked perfectly for me.

Here they used the below expression for ranking the products,

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

Thank you for replying !

Regards,

Krutika