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.
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
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.
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.
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.
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.
