Forum Discussion
Count more than 3 consecutive values
Hi!
Hope you can help me, this is what I am looking for:
I have a column called Result, and what I would like to have is a measure that count more than 3 positive consecutive values per product. If you see product "10 or" has consecutive values on weeks (4,5,6), weeks (9,10,11,12) and weeks (16,17,18,19). The condition is that has to be more than 3 consecutive weeks to be counted as 1 value.
The output for this example is Product "10 or" has 3 consecutives values and BIZ has 2 consecutives values. I tried to add a column "Consecutive" to help you to understand. If you see more than 3 consecutive "true" is counted as 1.
| 3 | 1/13/2019 | 10 or | 57 | False |
| 4 | 1/20/2019 | 10 or | 60 | True |
| 5 | 1/27/2019 | 10 or | 63 | True |
| 6 | 2/3/2019 | 10 or | 64 | True |
| 7 | 2/10/2019 | 10 or | 64 | False |
| 8 | 2/17/2019 | 10 or | 60 | False |
| 9 | 2/24/2019 | 10 or | 61 | True |
| 10 | 3/3/2019 | 10 or | 62 | True |
| 11 | 3/10/2019 | 10 or | 63 | True |
| 12 | 3/17/2019 | 10 or | 64 | True |
| 13 | 3/24/2019 | 10 or | 56 | True |
| 14 | 3/31/2019 | 10 or | 48 | False |
| 15 | 4/7/2019 | 10 or | 46 | False |
| 16 | 4/14/2019 | 10 or | 49 | True |
| 17 | 4/21/2019 | 10 or | 51 | True |
| 18 | 4/28/2019 | 10 or | 55 | True |
| 19 | 5/5/2019 | 10 or | 70 | True |
| 20 | 5/12/2019 | 10 or | 60 | False |
| 21 | 5/19/2019 | 10 or | 56 | False |
| 22 | 5/26/2019 | 10 or | 53 | False |
| 23 | 6/2/2019 | 10 or | 53 | False |
| 24 | 6/9/2019 | 10 or | 56 | True |
| 25 | 6/16/2019 | 10 or | 55 | False |
| 26 | 6/23/2019 | 10 or | 59 | True |
| 27 | 6/30/2019 | 10 or | 59 | False |
| 28 | 7/7/2019 | 10 or | 54 | False |
| 29 | 7/14/2019 | 10 or | 56 | True |
| 30 | 7/21/2019 | 10 or | 56 | False |
| 2 | 1/6/2019 | BIZ 10 | 64 | False |
| 3 | 1/13/2019 | BIZ 10 | 72 | True |
| 4 | 1/20/2019 | BIZ 10 | 72 | False |
| 5 | 1/27/2019 | BIZ 10 | 77 | True |
| 6 | 2/3/2019 | BIZ 10 | 100 | True |
| 7 | 2/10/2019 | BIZ 10 | 66 | False |
| 8 | 2/17/2019 | BIZ 10 | 93 | True |
| 9 | 2/24/2019 | BIZ 10 | 76 | False |
| 10 | 3/3/2019 | BIZ 10 | 85 | True |
| 11 | 3/10/2019 | BIZ 10 | 92 | True |
| 12 | 3/17/2019 | BIZ 10 | 97 | True |
| 13 | 3/24/2019 | BIZ 10 | 71 | False |
| 14 | 3/31/2019 | BIZ 10 | 75 | True |
| 15 | 4/7/2019 | BIZ 10 | 79 | True |
| 16 | 4/14/2019 | BIZ 10 | 73 | False |
| 17 | 4/21/2019 | BIZ 10 | 72 | False |
| 18 | 4/28/2019 | BIZ 10 | 59 | False |
| 19 | 5/5/2019 | BIZ 10 | 49 | False |
| 20 | 5/12/2019 | BIZ 10 | 85 | True |
| 21 | 5/19/2019 | BIZ 10 | 67 | False |
| 22 | 5/26/2019 | BIZ 10 | 68 | True |
| 23 | 6/2/2019 | BIZ 10 | 58 | False |
| 24 | 6/9/2019 | BIZ 10 | 73 | True |
| 25 | 6/16/2019 | BIZ 10 | 70 | False |
| 26 | 6/23/2019 | BIZ 10 | 79 | True |
| 27 | 6/30/2019 | BIZ 10 | 78 | False |
| 28 | 7/7/2019 | BIZ 10 | 79 | True |
| 29 | 7/14/2019 | BIZ 10 | 88 | True |
| 30 | 7/21/2019 | BIZ 10 | 89 | True |
Hope you can help me.
1 Reply
It is missed the top titles:
Week,Date,Product,Result,ConsecutiveTrue
The column Result has to have 3 consecutive positive values to be counted as 1. Or if you would like to see it in other way, the column ConsecutiveTrue must have 3 consecutive True to be counted as 1.
