Find Reoccurring Results

[Yatesaj8]

I'm trying to figure out how many consecutive weeks a customer appears in a report. For example, in the below screenshot, customer 1 appears for 4 consecutive weeks (5 through 8). Customers 2 and 3 only appear once. The actual data is obviously much more complex and I would love a way to automate in Alteryx. The other issues is that a customer may appear multiple times in the same week or appear in non consecutive weeks. I don't know if that changes how your would approach the problem.

Customer	Week
1	5
1	6
1	7
1	8
2	2
3	4

[apathetichell]

corrected to allow for the same customer to visit twice in one week. note - this assumes that the weeks are laid out as integers.

[Yatesaj8]

Thanks for the update! When I try to apply this to my data, I get an error "formula: invalid type in subtraction operator". It says the issue is the below (bod red) "-1". Any thoughts?

if [Row-1:Week]=null() then 1 elseif [Row-1:Week]=[Week]-1 then [Row-1:in-a-row]+1 else [Row-1:in-a-row] endif

[Yatesaj8]

Maybe this would be better sample data since it has repeat weeks and gaps.

Customer	Week
1	1
1	1
1	2
2	1
2	2
3	1
1	3
2	3
3	1
1	5
1	6

[apathetichell]

I believe it is saying that your [week] field is a string.  You can wrap each reference to [week] in a tonumber() function or you can convert the entire column prior to running the multi-row.

[Yatesaj8]

That worked for solving that particular error, but I'm still getting an "in a row" number that seems way too high. I only have two possible weeks in my data for now and I'm getting "in a row" output in the 100s due to a large number of duplicates.

[apathetichell]

weird. any chance you could post some additional data? and can you confirm that you haven't changed the group by option on multi-row formula. I added 100 dummy rows of customer 1 visiting in week 1 and didn't have any issues.

[Yatesaj8]

I have attached a cleaned data file that should provide you with the same basic info that I was using. Thanks so much for your help!

[apathetichell · Accepted solution]

add a unique prior to the mutl-row and select both cust# and week - that means there will be only one joint occurence of each.

I hadn't realized the week numbers would go down and then back up.

[Yatesaj8]

Thank you SO much! I really appreciate it.

If you have a second, could you explain what that formula is? This is my first day in Alteryx so even looking at the formula, I don't know what it is saying.