Rank Function Considering Date Value

[polisetti]

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

[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]

At the chart level.

[polisetti]

@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]

@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