Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Sequencing a series of dates and calculating the time variance

Martin_Miller
6 - Meteoroid

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! 

 

 

3 REPLIES 3
Kenda
16 - Nebula
16 - Nebula

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?

GavinAttard
11 - Bolide

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. 

 

 

Alteryx Everything, Leave no one behind.
Martin_Miller
6 - Meteoroid

That was the trick...Thank you!

Labels