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

Count consecutive days

rof6024
5 - Atom

I have a large set of customer data, and I need to get the longest number of consecutive visited days for each customer, as well as, the date of their most recent visit. The most recent visit is easy, but I'm having trouble calculating the longest consecutive visit.

For instance a customer might have visits for Jan 1st 2017 through Jan 9th 2017 (9 consecutive days) then not visit again until Feb 1st 2017 and visit for 10 days (Feb 1st - 10th) That same customer might then miss a few days and then visit again for 3 consecutive days. (Feb 20th - Feb 20rd)

 

Essential I would like to end up with three fields Cust ID | Most consecutive visits | Last visit

 

Data looks like this

Visit DateCust ID
01/01/20171111
01/01/20175555
01/02/20175555
01/03/20171111
01/04/20171111
01/04/20175555
01/04/20179999
01/05/20171111
01/06/20171111
01/06/20175555
01/06/20179999
01/07/20171111
01/07/20175555
01/07/20179999
01/08/20171111
01/08/20175555
01/09/20171111
01/10/20175555

 

Cust IDLongest Consecutive VisitLast Visit
1111701/09/2017
5555301/10/2017
9999201/07/2017

 

 

7 REPLIES 7
nick_ceneviva
11 - Bolide

The attached workflow should do the trick!  If you sort by the customer ID and then the Visit date, you can then creating a running total of consecutive visits using the Multi Row Formula.  Then using the summarize tool you can get the maximum number of consecutive and the most recent visit.

 

Hope this helps and let me know if you have any questions!

NicoleJohnson
ACE Emeritus
ACE Emeritus

See attached for a possible solution! Uses a few Multi-Row tools to determine if a date is consecutive to the one before & after, and then a few Summarize tools to group/sum/max to get the results you desire... hope that helps!

 

Cheers,

NJ

 

EDIT: Though @nick_ceneviva solution, while the same concept, is way more streamlined... you should use that one :)

rof6024
5 - Atom

Thanks for the reply Nick! I'm running version 11.0.5.26351 It wouldn't let me open the workflow

nick_ceneviva
11 - Bolide

I changed the version so you should be able to open the attached workflow!  Sorry about that

rof6024
5 - Atom

Thank you both for replying! Nicole I wasn't able to open yours as well. However, Nick's is working.

 

Nick,

In the multi-line formula I can't tell how its determining that the new consecutive days count is greater or less than the existing. (It seems to be working though). I'm just trying to learn. can you explain?

nick_ceneviva
11 - Bolide

So its not actually checking against other consecutive streaks.  It is just a running total and then the summarize tool takes the max consecutive days.  So the output would look something similar to this coming out of the Multi Row Formula tool:

DateConsecutive Visits (From Multi Line)
1/1/2017 1
1/2/2017  2
1/4/2017  1
1/5/2017  2
1/6/2017  3
1/7/2017  4
1/15/2017  1
1/16/2017  2
1/17/2017  3

 

If all of the above records are for the same customer, the max consecutive visits is 4.  Hope that helps!

chris_rowntree
5 - Atom

This worked for me, thanks! 

Labels