Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Counting days between dates on row data properly

jbuszin
8 - Asteroid

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 AVariable BVariable CVariable DDesired Variable D
5/21/20191777
5/28/20191777
6/4/20191777
6/11/201907[Null][Null]
6/18/201917714
6/25/20191777
5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

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
15 - Aurora
15 - Aurora

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.

 

image.png

 

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
8 - Asteroid

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 AVariable BWhat the proposed solution got (Variable DShould be Variable D
4/2/2019177
4/9/2019122
4/11/2019155
4/16/2019122
4/18/2019155
4/23/2019133
4/26/2019144
4/30/2019177
5/7/2019177
5/14/2019177
5/21/2019177
5/28/2019177
6/4/2019177
6/11/2019177
6/18/20190  
6/25/201911414
7/2/2019177
7/9/2019166
7/15/2019111
7/16/2019177
7/23/2019177
7/30/2019122
8/1/2019155
8/6/2019166
8/12/2019100
8/12/2019111
8/13/2019100
8/13/2019177
8/20/2019177
8/27/2019122
8/29/2019155
9/3/20190  
9/9/20191211
9/10/2019101
9/10/2019170
9/17/2019177
9/24/2019177
10/1/2019167
10/7/2019116
10/8/2019171
10/15/2019177
10/22/2019177
10/29/20191147
11/12/20191714
11/19/2019127
11/21/20190  
11/21/2019182
11/25/2019114
11/26/20190  
12/3/20191148
12/10/20190  
12/17/20190  
12/24/201911321
12/27/2019143
12/31/2019174
1/7/2020177
1/14/2020177
1/21/20200  
1/21/20200  
1/28/20200  
1/31/202011817
jarrod
ACE Emeritus
ACE Emeritus

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

 

jarrod_0-1588878058292.png

 

jbuszin
8 - Asteroid

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

Labels