Counting days between dates on row data properly

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

Tips and Tricks

Date Time

Data Investigation

Accepted answers

jarrod

@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!

Counting Days_Jarrod.yxmd

All comments

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.

Counting_Days_05052020.yxmd

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

20200505 - CountingDates.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703