Using Rank and Group in a Table based on max date

Question & Answer
, ,
1

I have created a table visualisation based on data with no amalgamation. Basically I need to do a number of filters and joins to create a data set which can be exported out of QuickSight.

image

What I now need to do is to only display the row for each user containing the most recent EnrollmentDate.

I’ve tried filtering, adding a calculated field using ifelse or max either in the analysis or the data set, or a simple rank, but I can’t find a way to do it.

The dates for each user name will vary, and there may be more than 2 enrollment dates depending on the user record

2

Hi @ALWD and Welcome to the QuickSight Community!
You can use the below calculated field to recognize the most recent enrollment date. If you’d like to narrow down to just the most recent, you can replace your enrollment date column with this field.

maxOver({EnrollmentDate}, [LastName], PRE_AGG)

Let me know if you have any further questions or if this helps solve your request.

Thank you!

3

Thanks Duncan :slight_smile:

This pointed me in the right direction - I could then use an if/else statement to rank as 1 if most recent enrolment date and enrolment date were equal, and filtered to remove anything other than 1 in the rank

1 Like