Hi,
Would love some ideas on how to build a column that counts the number of recurring weeks based on a given week.
For example, I have a list of products with weeks purchased:
Product ID | Week of Year (Purchased) |
111 | 22 |
111 | 23 |
222 | 24 |
111 | 24 |
222 | 19 |
333 | 15 |
333 | 16 |
222 | 14 |
444 | 24 |
333 | 20 |
I want to calculate, the consecutive weeks an item was purchased from week 24. Therefore the new column would look like this based on the data above:
Product ID | Week of Year (Purchased) | Formula |
111 | 22 | 3 |
111 | 23 | 3 |
222 | 24 | 1 |
111 | 24 | 3 |
222 | 19 | 1 |
333 | 15 | 0 |
333 | 16 | 0 |
222 | 14 | 1 |
444 | 24 | 1 |
Appreciate the help!