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.
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