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.
Solved! Go to Solution.
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