Counting days between dates on row properly

[jbuszin]

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

[fmvizcaino]

Hi @jbuszin ,

Only to understand a little better while I develop a suggestion for you.

In your first line (bold), you are looking to the next line, subtracting the 4/9 in row 2 minus 4/2 in row 1 to get 7 days difference

But for 6/25, you are subtracting 6/25 - 6/11 to get the difference, so you are looking for the previous row.

Variable A	Variable B	What the proposed solution got (Variable D)	Looking for previous	Looking for next
4/2/2019	1	7	-	7
4/9/2019	1	2	7	2
6/11/2019	1	7	2	14
6/18/2019	0
6/25/2019	1	14	14	7
7/2/2019	1	7	7

Which one are correct?

Best,

Fernando V.

[fmvizcaino · Accepted solution]

Hi @jbuszin ,

I've developed both examples and I'm attaching here for you to check.

It produces almost the same results as you specified with differences next to 0 values in variable B due to the things I mentioned in the previous post.

Take a look and let me know if you need further help.

Best,

Fernando Vizcaino

[jbuszin]

Fernando, sorry about this. I'm trying to get to "looking for next". Looks like you did both, and I'm grateful for that. I'll test it out and accept as the solution if it works out! thanks 

[jbuszin]

Fernando-this mostly worked. The issue was the dataset was bigger and had instances where there could be multiple rows where variable B was 0. So you created 2 rows in the multi-row formula tool but I looked into my data and saw there could base cases of 96 straight rows of 0s. So I simply did a look of if then else statements. thanks!