7 Replies Latest reply: Apr 13, 2017 11:12 AM by Kevin Chevrier

How to use if condition in rank percentile expression?

Hi,

I wanted to give a score in the range of 1 to 5 for rank percentile(R_Rank) expression. I created this expression using R integration with Qlik sense desktop. The problem is "if condition" doesn't work in the way I think in my percentile(R_Rank) variable because while executing the following condition:

if(rank(Recency)/length(Recency) > 0.8, 5,

if(rank(Recency)/length(Recency) > 0.6 and rank(Recency)/length(Recency) < 0.8, 4,

if(rank(Recency)/length(Recency) > 0.4 and rank(Recency)/length(Recency) < 0.6, 3,

if(rank(Recency)/length(Recency) > 0.2 and rank(Recency)/length(Recency) < 0.4, 2, 1))))

Note: I used R script inside if statement, also it is cut shorter to make the if statement simple and all the columns in the image is expression fields.

After a long try, I found Qlik is not considering all the record while if condition execution. If that is so, then the calculation for each record be like 1/1 = 1 then it gives me 5. instead of calculating the actual rank/total records.

I also tried to do the same without integrating R, I have displayed that too in the 3rd column. It doesn't have any idea how to convert this into Rank percentile. After that, I can easily give the score.

Column recency is the date difference today and Bill date. And the table in the image is pivot table. For your convenience, I have attached first two column excel file.

• Re: How to use if condition in rank percentile expression?

Change Length to Simple Len() with  in Qlikvew and then check

• Re: How to use if condition in rank percentile expression?

Hi Anil,

Length function inside if condition is R script.

• Re: How to use if condition in rank percentile expression?

Seems, you are working with Qliksense, We have Only Len() function for Qlik Products.

Here, What was the challenge you are looking. What was the variable name and where do you use. Finally, What was the issue. Is that condition is not working for you?

• Re: How to use if condition in rank percentile expression?

I like said in the question, I have created the rank percentile that you can see in column 2. take a look at last column, I used the if condition which is like I quoted in question, it throws me the output as 5.
It is throwing 5 because while executing the if condition it isn't considering the whole record, it is considering it as a single record and executing the if condition like 1/1 = 1 which is greater than 0.8 and it gives me 5.

My question is how can I overcome this issue?

• Re: How to use if condition in rank percentile expression?

Like I said in the question, I have created the rank percentile that you can see in column 2. take a look at last column, I used the if condition which is like I quoted in question, it throws me the output as 5.
It is throwing 5 because while executing the if condition it isn't considering the whole record, it is considering it as a single record and executing the if condition like 1/1 = 1 which is greater than 0.8 and it gives me 5.

My question is how can I overcome this issue?

• Re: How to use if condition in rank percentile expression?

Would you share QVF file with us please? Because, I even don't know what you did over there

• Re: How to use if condition in rank percentile expression?

You should try :

if(Column(2) > 0.8, 5,

if(Column(2) > 0.6 and Column(2) < 0.8, 4,

if(Column(2) > 0.4 and Column(2) < 0.6, 3,

if(Column(2) > 0.2 and Column(2) < 0.4, 2, 1))))

or maybe :

if(aggr(Column(2),Customer) > 0.8, 5,

if(aggr(Column(2),Customer) > 0.6 and aggr(Column(2),Customer) < 0.8, 4,

if(aggr(Column(2),Customer) > 0.4 and aggr(Column(2),Customer) < 0.6, 3,

if(aggr(Column(2),Customer) > 0.2 and aggr(Column(2),Customer) < 0.4, 2, 1))))

Kevin