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

Intervals between dates in different rows

shangaz
5 - Atom

Hi all,

 

I have a database with all the transactions of a set of customers. One important parameter for us is to understand each client's interval between two consecutive visits. In out database, each row is one visit as in the example below:

 

Client Visit
1 5-Jan
1 10-Jan
1 20-Jan
2 10-Jan
2 30-Jan

 - For client 1, the interval between his 1st and 2nd visits were 5 days and between his 2nd and 3rd visits 10 days.

- For client 2, with only two visits, the interval was 20 days

 

Given that the number of visits can go up to hundreds, does anybody know how could I have another column calculating the number of days since the last visit?

 

Thank you in advance!

 

Best,

Vinicius

8 REPLIES 8
s_pichaipillai
12 - Quasar

@shangaz

 

take a look at the attached workflow which may give you an idea how you can proceed

hope this is what your are looking for

s_pichaipillai
12 - Quasar

this could be the correct one

 Interval.PNG

JoeM
Alteryx Alumni (Retired)

Shangaz,

Assuming your DB also carries date with a year, I would first convert your date to be a date data type in alteryx, then use the multi-row formula and leverage the datediff() function. 

2016-02-23 23_07_41-Alteryx Designer x64 - VisitIntervals.yxmd_.png

shangaz
5 - Atom

@JoeM and @s_pichaipillai,

 

Thank you very much for your answers!

Coincidentally, my Alteryx stopped working today, but as soon as it comes back, I'm going to test your solutions and give you a feedback!

 

Best,

DHB
8 - Asteroid

How did you get the first date for Client 2 to not be the Interval from Client 1's last date?  I can't see it in the workflow but the screen shot shows it has been done.

DHB
8 - Asteroid

I got it.  It's group by function. 

sackerman70
5 - Atom

Hi,

 

I found this when I was searching how to do something similar. I want to see the time difference between guest stays in a hotel. I almost have it, but because of the way my data is structured, the first stay is not calculating as a 0. I am trying to determine the # of days between the departure of the previous stay and the arrival of the next stay.

 

stay data.PNG

 

I have attached my workflow. This is what my output currently looks like. The 2nd stays are calculating properly, but the 1st stays appear to be using the difference between the arrival date and departure date from the same line. I can't figure out how to make it calculate 0 for the order of stay with a value of 1.

 

stay data output.PNG 

 

Thank you for your help!

 

Sarah

 

sackerman70
5 - Atom

Apologies, I think I replied to the wrong user so I am replying to this so that you hopefully see my question....

 

Sarah

Labels