Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Rank Function not including both -ve and +ve values

I am trying to get the bottom 10 service levels for our Items supplied by various vendors. which is basically Supply vs demand for each vendor for YTD. I am using the Rank function as mentioned below

=AGGR(IF(Rank(-(Sum(AVAILABLE)/Sum (DEMAND)),4)<=10,ITEM), ITEM)

The top 10 works fine. but for bottom 10 i get just 1 record where the available is in -ve. therest 9 records do not show up.

if i create a simple table and i check the records, there are various items which satisfy the criteria and they should show up.

1 Solution

Accepted Solutions

Re: Rank Function not including both -ve and +ve values

See if this helps

=Aggr(If(Num(Rank(-(Sum(AVAILABLE)/Sum (DEMAND)),4)) <= 10, ITEM), ITEM)

12 Replies

Re: Rank Function not including both -ve and +ve values

I would suggest you to create a straight table with

Dimension

ITEM

Expressions

Rank(-(Sum(AVAILABLE)/Sum (DEMAND)),4)

Num(Rank(-(Sum(AVAILABLE)/Sum (DEMAND)),4))

See if there are 10 different items where the second expression with Num has value less than and equal to 10

Not applicable

Re: Rank Function not including both -ve and +ve values

Hi Sunny,

Thanks for the quick reply. There are different items with the second expression with num has values less than or equal to 10 but the first expression just has 2 values.

Re: Rank Function not including both -ve and +ve values

Can you share an image of this chart that you just created?

Not applicable

Re: Rank Function not including both -ve and +ve values

Untitled1.jpg

Re: Rank Function not including both -ve and +ve values

I was more interested in seeing rows where the second expression is less than 10

Not applicable

Re: Rank Function not including both -ve and +ve values

Somehow it's not sorting in Qlik, so i exported in excel and took a snapshot.Rank.png

Re: Rank Function not including both -ve and +ve values

See if this helps

=Aggr(If(Num(Rank(-(Sum(AVAILABLE)/Sum (DEMAND)),4)) <= 10, ITEM), ITEM)

Not applicable

Re: Rank Function not including both -ve and +ve values

Thanks Sunny,

This is showing the bottom 10 items but if i try add the service level expression . it just show

Sum (AVAILABLE)/Sum (DEMAND). It is showing values only for the negative one and for the rest it is 0Rank2.png

Re: Rank Function not including both -ve and +ve values

My guess is that those values are 0 and that is why we see 2-10 Rank for them.

Community Browser