Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
@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?
At the chart level.
@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
@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