cancel
Showing results for 
Search instead for 
Did you mean: 

Sequencing a series of dates and calculating the time variance

SOLVED
Highlighted

Hello Community!

 

I'm looking for some help to take a series of status change dates made against a record and record the time that record was held in the respective status'.

 

I have attached a screen shot of a single Work Order record and have summarized the status change dates and sorted the dates in ascending order.

 

Is there a way to take the next status date in the series and "create a new row" that captures the next date in the time series to generate a date / time calculation?

 

For example, I want to see the time it took to go from WAPPR (2016-10-04) to WENG (2016-10-04) - ~24minutes.  This would require me to pull in the change date of WENG to a NEW column next to the previous status of WAPPR - or so this is my mental model (image attached for reference).

 

Alteryx Community - WO Time Series.JPG

Thanks in advance for the help! 

 

 

BarnesK
Bolide

Hey @Martin_Miller! If you add a Multi-Row Formula tool and create a new field with the following Expression:

DateTimeDiff([changedate],[Row-1:changedate],"minutes")

Does that accomplish what you're wanting?

Hi @Martin_Miller

 

The Cross tab tool should do the trick. 

 

Group on Work Order. Name from Status and value from change date. 

 

Example attached. 

 

 

That was the trick...Thank you!