ID DateTime Status Name
001 Jan 1 2023 01:02:00 S100 TestName1
001 Jan 2 2023 01:02:00 S200 TestName1
001 Jan 3 2023 01:02:00 S300 TestName1
001 Jan 4 2023 01:02:00 S400 TestName1
001 Jan 5 2023 01:02:00 S500 TestName1
002 Jan 1 2023 01:02:00 S100 TestName2
002 Jan 2 2023 01:02:00 S300 TestName2
002 Jan 3 2023 01:02:00 S500 TestName2
003 Jan 1 2023 01:02:00 S200 TestName3
003 Jan 2 2023 01:02:00 S300 TestName3
003 Jan 3 2023 01:02:00 S500 TestName3
004 Jan 1 2023 01:02:00 S200 TestName4
004 Jan 3 2023 01:02:00 S300 TestName4
Hello, I have a dataset with above shown data. My requirement is to get records with specific Status only if that is the latest record for each ID for a selected DateTime range. Example, I need to pull all records with Status = S300 only if that is the latest status between Dec 31 2022 to Jan 3 2023.
I added a filter on DateTime to filter the records on the given date time.
Then added a calculated field Rank to rank the records with same ID in the descending order of DateTime so latest record will have a rank 1. rank([{DateTime DESC}],[{ID}],PRE_AGG)
Now when I apply the filter, DateTime between Dec 31 2022 to Jan 3 2023, Status = S300 and Rank =1, I get below records.
001 Jan 3 2023 01:02:00 S300 TestName1 rank=1
002 Jan 2 2023 01:02:00 S300 TestName2 rank=1
003 Jan 2 2023 01:02:00 S300 TestName3 rank=1
004 Jan 3 2023 01:02:00 S300 TestName4 rank=1
What I was expecting is below, because those are the only 2 which has a latest status of S300 within that date time range. The reason for above result is, rank is getting recalculated after applying all the filters.
001 Jan 3 2023 01:02:00 S300 TestName1
004 Jan 3 2023 01:02:00 S300 TestName4
I also tried to create another calculated field, RankPrefilter - rank([{DateTime DESC}],[{ID}],PRE_FILTER). This will rank all records before applying any filter, but this also will not work because even after applying the date filter, rank remains the same. So filtering rank=1 will not work.
Is there any way, I could apply the date filter first then rank and then apply any other filters that will not affect the rank. Or any other way to solve this. I would like the result to be used in various charts like PKI, bar and tables.