Previous Timestamp Value recorded by machine

Hello,

i would like to add a calculated field on my dataset with the previous timestamp recorded by a machine on his daily activity.
Dataset presents following sturcture

machine_id/ start_activity / end_activity
machine_1/ 2023-03-01 22:45:12 / 2023-03-01 22:46:18
machine_2/ 2023-03-01 22:44:12 / 2023-03-01 22:48:18
machine_3/ 2023-03-01 22:43:12 / 2023-03-01 22:44:18
machine_4/ 2023-03-01 22:43:28 / 2023-03-01 22:43:53
machine_1/ 2023-03-01 22:44:12 / 2023-03-01 22:44:48
machine_2/ 2023-03-01 22:42:12 / 2023-03-01 22:42:18

idea is to add a calculated field in order to detect dead time

dead_time = start_activity - lastActivityDatestamp
LastActivityDatestamp = lastValue({end_activity}, [{end_activity} ASC], [{machine_id}])

but lastActivityDatestamp value returns me only Unabailable results

How can i fix it?

lastValue() gives the final value according to some timestamp ({end_activity}). You can get the previous timestamp using the lag() function:

   lag({end_activity}, [{end_activity} ASC], 1, [{machine_id}])
1 Like