I'm trying to create the far right column correctly below (should be Variable D). I have everything to the left of us. Variable D that I have created is the number of days between the rows from Variable A. Variable B tells me if something happened (a 1) or not (a 0). If something did not happen (as nothing happened on 6/18/2019), then I want Should be Variable D to be the number of days between a row where it's 1 on Variable B and then the previous row where Variable B is also 1. So while something was sent on 6/18/2019, nothing happened. So since something happened again on 6/25/19 the number of days between when a previous thing happened on 6/11 and 6/25 is 14. I got this solution but then the next instances aren’t right. See 9/3. I get a “2” when it should be “11.” Or the instance of 12/10 and 12/17 and 12/24….didn’t have a 1 on 12/10 or 12/17 but did on 12/24. So should be variable did is 21 but in actuality it’s 13. What am I doing wrong?
Variable A | Variable B | What the proposed solution got (Variable D) | Should be Variable D |
4/2/2019 | 1 | 7 | 7 |
4/9/2019 | 1 | 2 | 2 |
4/11/2019 | 1 | 5 | 5 |
4/16/2019 | 1 | 2 | 2 |
4/18/2019 | 1 | 5 | 5 |
4/23/2019 | 1 | 3 | 3 |
4/26/2019 | 1 | 4 | 4 |
4/30/2019 | 1 | 7 | 7 |
5/7/2019 | 1 | 7 | 7 |
5/14/2019 | 1 | 7 | 7 |
5/21/2019 | 1 | 7 | 7 |
5/28/2019 | 1 | 7 | 7 |
6/4/2019 | 1 | 7 | 7 |
6/11/2019 | 1 | 7 | 7 |
6/18/2019 | 0 | | |
6/25/2019 | 1 | 14 | 14 |
7/2/2019 | 1 | 7 | 7 |
7/9/2019 | 1 | 6 | 6 |
7/15/2019 | 1 | 1 | 1 |
7/16/2019 | 1 | 7 | 7 |
7/23/2019 | 1 | 7 | 7 |
7/30/2019 | 1 | 2 | 2 |
8/1/2019 | 1 | 5 | 5 |
8/6/2019 | 1 | 6 | 6 |
8/12/2019 | 1 | 0 | 0 |
8/12/2019 | 1 | 1 | 1 |
8/13/2019 | 1 | 0 | 0 |
8/13/2019 | 1 | 7 | 7 |
8/20/2019 | 1 | 7 | 7 |
8/27/2019 | 1 | 2 | 2 |
8/29/2019 | 1 | 5 | 5 |
9/3/2019 | 0 | | |
9/9/2019 | 1 | 2 | 11 |
9/10/2019 | 1 | 0 | 1 |
9/10/2019 | 1 | 7 | 0 |
9/17/2019 | 1 | 7 | 7 |
9/24/2019 | 1 | 7 | 7 |
10/1/2019 | 1 | 6 | 7 |
10/7/2019 | 1 | 1 | 6 |
10/8/2019 | 1 | 7 | 1 |
10/15/2019 | 1 | 7 | 7 |
10/22/2019 | 1 | 7 | 7 |
10/29/2019 | 1 | 14 | 7 |
11/12/2019 | 1 | 7 | 14 |
11/19/2019 | 1 | 2 | 7 |
11/21/2019 | 0 | | |
11/21/2019 | 1 | 8 | 2 |
11/25/2019 | 1 | 1 | 4 |
11/26/2019 | 0 | | |
12/3/2019 | 1 | 14 | 8 |
12/10/2019 | 0 | | |
12/17/2019 | 0 | | |
12/24/2019 | 1 | 13 | 21 |
12/27/2019 | 1 | 4 | 3 |
12/31/2019 | 1 | 7 | 4 |
1/7/2020 | 1 | 7 | 7 |
1/14/2020 | 1 | 7 | 7 |
1/21/2020 | 0 | | |
1/21/2020 | 0 | | |
1/28/2020 | 0 | | |
1/31/2020 | 1 | 18 | 17 |
