Max Timestamp data when a date is selected

Question & Answer
,
1

Hello,

I have 2 fields: id(int) and as_of_date (datetime) in my dataset.

Every x mins interval, there are multiple records getting inserted with same id and as_of_date. e

So at 2/13/2025 12:00 AM → with id = 1 → total 10 records get inserted.
At. 2/13/2025 12:15 AM → with id = 2 → total 20 records get inserted.

In my analysis I have AsOfDate filter which filters the date part of and shows data for that date. However, I would now like to only show the latest data. i.e. with id = 2. After 15 more mins, the data should be updated to that as of id = 3.

Effectively I want the all max timestamp records for a given date. How can I achieve that?
Please advise.
Thank you.

1 Like
2

Hello @harpar1808, just to clarify, are you wanting to display the max date for each ID, or the max date overall ignoring IDs?

maxOver is probably the way to go either way.

If you are filtering to today’s date, and only want to show the most recent timestamp for each ID, you can do this:
Max Timestamp by ID = maxOver({AsOfDate}, [{ID}], PRE_AGG)

Then you can write an ifelse statement to only display the max timestamp:
Display Max = ifelse({AsOfDate} = {Max Timestamp by ID}, {AsOfDate}, NULL)

You could also do something similar with a denseRank instead of a maxOver:
denseRank([{AsOfDate} DESC], [{ID}], PRE_AGG)

Then, you can just filter your visual to require the denseRank to be equal to 1.

Now, if this is for all IDs, you can exclude the ID field from the partition value on the calculations. Let me know if this helps!

3

Thanks. Yes, I did the denseRank method and so far so good.

Can my this calculated field span across N data-sets (assuming the underlying fields: id and as_of_date are present in the N data-sets) ?

4

Hello @harpar1808, as long as each dataset has the same fields and field values, you can make the calculated field on each dataset. Then you can apply a filter across datasets. Just make sure you name the field the same way on each!