# New to Qlik Sense

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

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for
Did you mean:
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 # Accounts Rank Date AAA 7 1 10/06/2020 BBB 6 2 09/15/2020 CCC 6 4 09/30/2020 DDD 6 3 10/20/2020 FFF 5 9 09/10/2020 GGG 5 7 09/16/2020 HHH 5 6 09/22/2020 III 5 5 09/28/2020 JJJ 5 8 10/13/2020 KKK 4 11 09/01/2020 LLL 4 10 09/14/2020
Labels (3)

• ### Ranking Help

7 Replies

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

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

Creator
Author

At the chart level.

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.

@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

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?

@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

Tags
Community Browser