Count consecutive weeks from today

[Yatesaj8]

I'm trying to get a count of how many consecutive weeks (including the current week) a customer appears on a list. If the customer does not appear in the current week, then they should count as 0. How would I write a formula to get this?

I currently have the data set up similarly to the left 3 columns. The desired results are in the right 2 columns. For the sample data below, we are currently in week 5.

Any help would be greatly appreciated. It's my first week in Alteryx and this kind of thing has me very confused.

				Correct Results
Customer	Week	Current week		Customer
1	1	5		1	5
1	2	5		2	2
1	3	5		3	0
1	4	5		4	1
1	5	5		5	2
2	1	5		6	0
2	2	5
2	4	5
2	5	5
3	4	5
4	2	5
4	3	5
4	5	5
5	4	5
5	5	5
6	2	5
6	3	5
6	4	5

[mceleavey · Accepted solution]

Hi @Yatesaj8 ,

I've done this by breaking out a little multi-row magic:

I determined the max week for each customer and used this to determine if they had a sequence that ran into current week. Then it was a simply matter of counting sequences from that number.

Workflow attached.

I hope this helps.

M

[Yatesaj8]

Thank you SO much! 

If you have a minute, would you mind explaining the formula for counting the sequences?