How to use if condition in rank percentile expression?

[mohan_1105]

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.

[Anil_Babu_Samineni]

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

[mohan_1105]

Hi Anil,

Length function inside if condition is R script.

[Anil_Babu_Samineni]

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?

[mohan_1105]

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?

[mohan_1105]

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?

[Anil_Babu_Samineni]

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

[kevinchevrier · Accepted solution]

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