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