Expression to find the closest date in a [timestamp] column with date that is less than the date in present row

[Arpan Tyagi]

I have a user column and a timestamp column, I am looking for the previous nearest timestamp of the same user, I have been banging my head over it for the last few hours but cant come up with an expression, can anybody help me

 

UserTimestamp Required Column

A 7/29/2021 3:55:06 AM 7/22/2021 3:55:06 AM

A 7/21/2021 3:55:06 AM None

A 7/22/2021 3:55:06 AM 7/21/2021 3:55:06 AM

B 7/26/2021 3:55:06 AM None

B 7/28/2021 3:55:06 AM 7/26/2021 3:55:06 AM

C 7/21/2021 3:55:06 AM None

B 7/29/2021 3:55:06 AM 7/28/2021 3:55:06 AM

[Fabian Duerr]

First, create a cloumn with the rank of the dates per user:

Rank([Timestamp],[user])Then use the following aggregation. Here, MAX could be replaced with any other aggregation method as long as there is only a single occurence for each RANK per user:

Max([Timestamp]) OVER (Intersect([user],Previous([RANK])))