Counting days between dates on row data properly

[jbuszin]

I'm trying to create the far right column correctly below (Desired Variable D). I have everything to the left of us. The Variable C is the # of days between a given row and the row previous. Variable D that I have created is the same except variable B tells me if something happened (a 1) or not (a 0). If something did not happen (as nothing happened on 6/11/2019), then I want Desired 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/11/2019, nothing happened. So since something happened again on 6/18, the number of days between when a previous thing happened on 6/4 and 6/18 is 14.

Variable A	Variable B	Variable C	Variable D	Desired Variable D
5/21/2019	1	7	7	7
5/28/2019	1	7	7	7
6/4/2019	1	7	7	7
6/11/2019	0	7	[Null]	[Null]
6/18/2019	1	7	7	14
6/25/2019	1	7	7	7

[JosephSerpis]

Hi @jbuszin Edit: realized my initial solution would not work if you had multiple weeks with 0 for Variable B so changed my workflow.

[Jonathan-Sherman]

Hi @jbuszin,

I couldn't wrap my head around the 1 tool solution for some reason so decided to break it out into a few steps instead.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[jbuszin]

thank you guys for the proposed solution. It's not quite working. Let me paste below what you sent got me, and another column for what it should be (note I took at variable C as I don't think it's needed):

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

[jarrod · Accepted solution]

@jbuszin Something doesn't look right with what you posted in response - i must be missing something. Why does row 2 show a difference of 2 when the number of days between 4/2 and 4/9 is 7? same with the row 3. To me, it looks like it should be 2 days between 4/9 and 4/11, but you have 5 days. down around row 9 is when it starts falling in line again (5/7 to 5/14 is a 7 day difference). i put together the attached workflow and it says i'm off on 20 of the records. 

What i did was just remove the "0" rows and calculate a datediff on a single row above, then put the data back together and sorted. Like i said, i might have missed some logic here, but let me know if this helps. Thanks!

[jbuszin]

thanks! I believe I started another thread to get the issue addressed and between that solution and seeing this, we got it figured out. thanks again for the help