Calculate elapsed time between values in different rows

Question

I need to determine the elapsed time from case start date to case close date for each unique case ID. Case ID, DateTime, and Description are in separate columns. Looking for recommendation on the best method to calculate elapsed time between the highlighted values for each case ID. Ideally I would do this by adding another column for the calculated "Time To Close" and put this value in every row with "CLOSE_CASE_EVENT" in the OBJCT_OPER_DESC column.

Simplified workflow example attached.

Example_TimeToCloseCase.yxmd

ThizViz · Accepted Answer

There may be a better way, but thinking it through...

Create filters for just the New Case lines and the Close Case lines. Join them on Case ID, which should give you:

Case ID, New Case Date, Case Close Date

Then create a column using a formula Case Close Date - New Case Date

Join that back to your original data using CaseID. This will give you time to close in every row, but you can update it with a formula

If OBJCT_OPER_DESC = "CLOSE_CASE_EVENT" THEN [TimeToClose] else NULL ENDIF