topic Re: Rank order with Concatenation? in QlikView

Rank order with Concatenation?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Using below expression able to get top 5 Store Name in straight table, Is it possible to rank the store names according to highest sold/sales instead of alphabetical order? Thanks

=Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) <=5, StoreName), ArticleNo, StoreName), ', ')

Best Regards,

Deva

Re: Rank order with Concatenation?

Fri, 21 Jul 2017 04:40:26 GMT

Hi,

For top 5 stores give 5 different expressions each one for first,second,third,4th and 5th shops.

for First store expression will be =Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) =1, StoreName), ArticleNo, StoreName))

2nd store =Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) =2, StoreName), ArticleNo, StoreName))

3rd store =Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) =3, StoreName), ArticleNo, StoreName))

4th store =Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) =4, StoreName), ArticleNo, StoreName))

5th store -=Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) =5, StoreName), ArticleNo, StoreName))

Re: Rank order with Concatenation?

antoniotiman — Fri, 21 Jul 2017 04:42:32 GMT

Hi,

try

=Concat(DISTINCT Aggr(If(Rank(Sum(SoldQty),4) <=5, StoreName), ArticleNo, StoreName), ', ',Aggr(-Sum(SoldQty),ArticleNo,StoreName))

Regards,

Antonio