community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Calculate elapsed time between values in different rows

Highlighted
Atom

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.

SnipImage.JPG

ACE Emeritus
ACE Emeritus

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

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Labels