Filter data on max datetime for each id’s

Hi ,

I have a requirement where is need to filter data on max date for each id’s

my data looks this

id datetime amount
300408 10/13/2021  5:46:38 AM 120
300408 1/17/2022  3:58:26 AM 200
300411 10/27/2021  10:57:19 AM 312
300411 2/17/2022  3:58:26 AM 400
300529 20/5/2021  11:57:19 AM 102
300529 9/3/2021  9:22:00 PM 201
300529 1/21/2022  01:43:31 AM 100

my desired output is

 
id datetime amount
300408 1/17/2022  3:58:26 AM 200
300511 2/17/2022  3:58:26 AM 400
300529 1/21/2022  01:43:31 AM 100

I know i can get max date with using max(datetime) on measure but i can’t use measure for filtering

as we want the records to always picked for latest date without user filter.

0 2 1,271
2 REPLIES 2

Dawid
Participant V

You need to do some pre-processing before you apply such filter in Looker. Either for each ID add “most recent” field in your Data Model, which then will allow you to create a yesno dimension when you compare most_recent with all the datettimes, or add a ROW_NUMBER() to your data partitioned by ID and order by datetime descending, then you can use a filter rn = 1

Thanks @Dawid 

Top Labels in this Space
Top Solution Authors