topic Re: Rank () is not working as expected. in QlikView

Rank () is not working as expected.

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

Hello all,

i have one pivot table chart in which i am showing data based on TOP 20 Negetive Difference.

now table now showing 2nd rank, it is showing 1,3,4,5,6,7 and so on.....

Can any one suggest?

Thanks in advance.

Re: Rank () is not working as expected.

sunny_talwar — Mon, 11 Sep 2017 12:20:51 GMT

What is the calculated dimension used to calculate Rank?

Re: Rank () is not working as expected.

infosense_devel — Mon, 11 Sep 2017 12:25:26 GMT

thanks for reply...

we have to rank our customer_group based on "Difference" column (third from the right in screenshot), so we are using same expression which we are using in "Difference" column and we are just aggr() it by "Customer Group" dimension.

Re: Rank () is not working as expected.

infosense_devel — Mon, 11 Sep 2017 12:34:46 GMT

=aggr(rank(

CurrentYear_Invoice

PY_Invoice

))

*-1),Customer_Group)

Re: Rank () is not working as expected.

sunny_talwar — Mon, 11 Sep 2017 12:34:53 GMT

great, but what is the expression

Re: Rank () is not working as expected.

sunny_talwar — Mon, 11 Sep 2017 12:37:47 GMT

Did you try creating a new chart with

Dimension

Customer_Group

Expression

Rank(CurrentYear_Invoice - PY_Invoice))*-1)

and then see that are you getting RANK 2 for any Customer_Group or not?

Re: Rank () is not working as expected.

Anonymous — Mon, 11 Sep 2017 12:38:14 GMT

=aggr(rank (only{1}(CurrentYear_Invoice-PY_Invoice))*-1) ),Customer_Group)

Re: Rank () is not working as expected.

its_anandrjs — Mon, 11 Sep 2017 12:40:35 GMT

Hey did you use more dimensions on this chart where you calculate rank.

Re: Rank () is not working as expected.

hic — Mon, 11 Sep 2017 12:42:43 GMT

Rank() is tricky...

It can skip number "2" if there is more than one that ranks highest.

Further, you seem to have multiple dimensions. Then you need to be on top of whether the Rank() refers to the last dimension only or whether it is for the total. See picture below.

HIC

Re: Rank () is not working as expected.

jonathandienst — Mon, 11 Sep 2017 12:46:32 GMT

Depending on the parameters of the Rank expression, it looks like you have a tie for first place or for second place. The optional 3rd parametert determines how ties are handled (rank up or down).

Re: Rank () is not working as expected.

infosense_devel — Mon, 11 Sep 2017 12:46:49 GMT

Got the reason.. customer_group is "-" for rank 2.....

as soon as i removed all dimension from table and added again it shows rank 2 with "-".

Re: Rank () is not working as expected.

its_anandrjs — Mon, 11 Sep 2017 12:55:45 GMT

Check the attached qvw file for just explaining Rank functionality but it does not seems the "-" will effect your rank kindly check your expression with one by one adding the dimension and check with the expression also.

I agree with the HIC here also