Use of Aggregation and Rank

[Manish_Kumar_]

I have data in this format:

Transaction_Timestamp,User_Id

1Dec22 04:00:00,ID1

5Dec22 05:00:00,ID1

5Dec22 07:00:00,ID1

10Dec22 04:00:00,ID1

I want a Chart in below format:

Transaction_Date,No_Of_Users

1Dec22,0

5Dec22,1

10Dec22,0

Here No_Of_Users is count of users who make their 2nd deposit on that particular Transaction_Date

To be more clear, Say in this case we have no users whose second Transaction is on 1st Dec and Similarly for 10th Dec. Whereas for 5thDec22 there has been 2 transactions..one of which was 2nd transaction and other was 3rd.

So we count No_Of_Users as 1 in the case of 5thDec22 .

I want this outcome on frontEnd Chart! Anybody with some idea Please Suggest!

[AronC · Accepted solution]

This expression should do it for you

sum(Aggr(if(Rank(-Transaction_Timestamp)=2,1),User_Id,Transaction_Timestamp))

Please mark as solution if it works for you.