Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator
Creator

Rank Function Considering Date Value

Hi Everyone,

I am using Rank function in my expression. Below is the table, where I am ranking Count(Accounts) for each person and showing the table. However, I need to consider Date while calculating the Rank function. For the same #Accounts, I need to consider the Min Date having lowest Rank. For Ex: BBB, CCC and DDD is having same #Accounts of 6 but the rank should consider the Date order while calculating. So BBB should be 2, CCC should be 3 and DDD should be 4. How to achieve that?

Current Rank Expression: Rank(Count(Distinct Accounts),4,0) and I kept number format.

Name# AccountsRankDate
AAA7110/06/2020
BBB6209/15/2020
CCC6409/30/2020
DDD6310/20/2020
FFF5909/10/2020
GGG5709/16/2020
HHH5609/22/2020
III5509/28/2020
JJJ5810/13/2020
KKK41109/01/2020
LLL41009/14/2020
Labels (3)
7 Replies
Kushal_Chawda

@polisetti  do you need it in script or chart expression?

Kushal_Chawda

@polisetti  also one question, why don't you chose account 4 and date 09/01/2020 as Rank 2?

polisetti
Creator
Creator
Author

At the chart level.

polisetti
Creator
Creator
Author

@Kushal_Chawda Firstly, I need the expression at chart level as already I have written Rank function in the Chart

Secondly, The rank should be based on the highest Accounts for each Name and for the Same count accounts, the rank should be considering the date order. So, the Name having 4 accounts will be ranked at 10 and 11 positions only but again the 10 position should be "KKK" because he has min date value compared to "LLL".

I hope this clarifies your question.

Kushal_Chawda

@polisetti  so according to your logic, should it not the 5 Rank goes to FFF as min date for that # account is 09/10/2020

polisetti
Creator
Creator
Author

@Kushal_Chawda Yes, the "FFF" should be Rank 5 as per my logic. But how to implement that is the question. Do you have any idea?

Kushal_Chawda

@polisetti  I tried below

With Date and Name in dimension put below expression

I have assumed that # account is the measure you can replace it with your actual measure in place of highlighted one.

=num(rank(total sum([# Accounts]),4))   // for rank

= sum([# Accounts])  // #account

Sort order is

1) #account -> Numeric desending

2) Date -> Numeric ascending

Screenshot 2020-09-24 212829.png