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

    How to use if condition in rank percentile expression?

    Mohan Kumar

      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.