How to calculate date difference for events when the date field is dynamic

Hi, wonder if someone can help as I am stumped (easily done)

I’m looking at video production data and the path of a video project between us (the production company) and the client is stored as events. The screengrab is for a particular project (readableId GUV2W3) and the client submits this project on the platform and this generates a version (versionNumber) which starts at 1. There are two events (eventType) I am looking at;
1 - PROJECT_OUTPUT_INTERNAL_APPROVED (this triggers the video output to be returned to the client)
2 - PROJECT_OUTPUT_REJECTED (this is when the client requests ammendments to the video project and ‘returns’ it which automatically generates the next version in the system i.e V2 then V3

From this table I can see that we took 4 days to return the first version and this is easy to create a calculated field as it’s datediff between submittedAt and the first EventCreatedAT. The submittedAt data is the intial submission date and, as you can see, this date is fixed and can be referenced in any event (the same as the completeAt date which is when the client marks the project as complete)

The next data I want to be able to put into a column is how long the client spent reviewing this V1. This is the difference between Aug 19 2024 EventCreatedAt and Aug 21 2024 EventCreatedAt…but how can I get this as a field? As the EventCreatedAt is linked to each event I can’t figure how I can reference the two separate EventCreatedAt dates which are linked to the two separate events (in this case row 1 and row 2). I then want to see how long we spent editing V2 which is the difference between Aug 21 and Aug 22 (rows 2 and 3) and so on and so on.

Any help much appreciated!

Hi @Turboslippers

Assuming you only have two events and there is always a approved/rejected cycle Lag and Lead functions probably can help you calculated the days you want.

The functions expect a measure so may be wrapping your date value (EventCreatedAT) with a Max may probably do the trick. I haven’t tried it myself but should mostly work.

Give it a try and let me know if there are issues.

Regards,
Giri

Hi @Giridhar.Prabhu, thanks for taking the time to look at this and respond, much appreciated :slight_smile:
Ah, I don’t think this method will work, if you look at the screengrab you can see for this example that are many events…this project went to Version 7 so there are events for internal approval and then client ‘rejection’ i.e 10+ events. I need to able to isolate the time/date for all the events so using a max won’t work will it as Max will be last event i.e V7 submitted to client so how to isolate the events related to V2,V3 etc?

regards
Ben

@Giridhar.Prabhu any thoughts? I’m completely stumped…

Hi @Turboslippers

Since you have the event on the pivot table Max is grouped by the dimensions so should consider each version.

Have you tried it?

Regards,
Giri