Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohan_1105
Partner - Creator III
Partner - Creator III

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.


Capture.JPG

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.

1 Solution

Accepted Solutions
kevinchevrier
Partner - Creator III
Partner - Creator III

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

View solution in original post

7 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mohan_1105
Partner - Creator III
Partner - Creator III
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mohan_1105
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kevinchevrier
Partner - Creator III
Partner - Creator III

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