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

Time period between different dates in different rows

Dario1
5 - Atom

Hello Guys, I need help to calculate the interval of days between two dates.

 

The status of one ticket should pass by stages and I need to know how many days was the ticket on each status.

 

TicketSubmitted dateWorked onStatus
AA1231/22/20191/31/2019 10:09A
AA1231/22/20192/1/2019 7:47B
AA1231/22/20192/8/2019 14:06C
AA1231/22/20192/26/2019 8:22D
AA1231/22/20193/4/2019 10:49E
BB1231/23/20192/8/2019 14:06B
BB1231/23/20192/10/2019 14:07C
BB1231/23/20192/11/2019 14:09D
BB1231/23/20192/12/2019 12:07E

 

Example in the ticket AA123 row 1 has passed 9 days between the submitted date and the work day, the second status was mas 1 day after and the 3rd row 7 days.

 

Do you know how can I calculate this ?

 

Also if you could add a formula to calculate this only in business days will be much appreciated.

 

Thanks in advance.

4 REPLIES 4
adamorse
9 - Comet

DateTimeDiff([firstdate],[laterdate],"days") will give you the number of days inbetween the first date and the later date. 

 

If you divide by seven you'll know how many weeks occurred, and rounding up and multiplying by two should give the number of weekends if I'm thinking this through right (assuming the dates are all occurring on business days).

The_Dev_Kev_Env
9 - Comet

Hi Dario,

 

You'd want to use the "multi-row" formula here. 

 

You would sort the data by Ticket and Worked On date. Then in multi row tool, create new field called days passed as an int. The formula would look something like this :

 

iif(isnull([Row-1:worked_on]),0,datetimediff([worked_on],[Row-1:worked_on],'days') +[Row-1:days_passed])

 

Best,

devKev

 

danilang
19 - Altair
19 - Altair

Hi @Dario1 

 

This ones a little tricky because for the first row in the Ticket, the elapsed is the difference between Submitted and Worked On and for the subsequent Status items, it the difference between the Worked on and the previous Worked on.  When you add on the fact that you're looking for business days and not calendar days, it becomes a multi-step process. 

 

This work flow gives you what you're looking for.  

WF.png

 

It generates a row for each of the dates between the start and end and then removes the weekends and holidays(must be considered if you're counting business days).  Count the number of rows remaining and you have your answer.

 

Results.png

 

Note that you may need to adjust the holiday list for your locale.

 

Dan

 

 

Dario1
5 - Atom
Thanks for your help and time.
You have solved my problem.
Bests.
Labels