Alteryx designer Discussions

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

Time elapsed between two dates in different rows

Highlighted
7 - Meteor

Hello all,

 

I am looking for a way to know the interval of days between two dates.

 

The status of one ticket should pass by different stages and I need to know how many days was the ticket on each status, kindly note that a ticket can repeat the same status, in that case I need to know the summary of the days with same status. so what I need is to know how many days are each ticket in each status.

 

TICKETTIMESTAMPSTATUS

123

2020-01-22A
2222020-01-22A
1232020-01-23B
3212020-01-24A
2222020-01-25B
1232020-01-25B
3212020-01-26B
1232020-01-30C
3212020-02-02C

 

Kindly note that Timestamp refers to the date when this information has been entered to the sheet.

 

With this scenario the results that I want is the below chart. (the numbers in the status columns are days)

 

TICKETSTATUS ASTATUS B
12317
22230
32127

 

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.

Highlighted
11 - Bolide

@Dario02 

 

Great question.

 

However, why do you have ticket 123 marked twice in Status B?

Sincerely,

 

Jacob

Highlighted
7 - Meteor

Yes, sometimes the status cannot be changed and it is tracked as the same status before.

 

I know that maybe I cannot get the chart I need. if so it is acceptable to have this below:

 

TicketStatusDays
123A#
123B#

 

Thanks for your time

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hello @Dario02,

 

Would this help?

 

Untitled.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Highlighted
11 - Bolide

@Dario02 

 

I've built you a workflow with a Batch Macro.

 

Please let me know if it helps. If it does, please accept this as a solution.

 

Thank you,

 

Jacob

Highlighted
12 - Quasar

slightly different (and more complicated) path, but one I believe is necessary in order to calculate business days.

 

In short:

Scaffold all the available dates in each ticket status, and then count the days that are business days.

 

 

Highlighted
7 - Meteor

Many thanks for all the help, I has been able to do what I want thanks to your help.

Highlighted
7 - Meteor

Unfortunately I was not able to open your workflow because I was using an older version of Alteryx.

 

But I appreciate your prompt reply and help.

 

I hope it could help somebody else in the future.

 

Have a great day.

Labels