among individuals counting activities between events

[jbuszin]

Could use some advice on how you would do this in alteryx. Below is data from a single person. The first column is a marker between events (for example, has a baby, and this person had 3 babies). The second column is the number of activities that happened between events. Sticking to the example, that lets call them doctor appointments. So, this person had 14 doctor appointments before baby 1, 26 after baby 1 and before baby 2, and 14 after baby 2 and before baby 3. Note the dataset actually has many individuals (with a unique ID) in there with different numbers of babies.

I need a third column that can tell me the total number of appointments between babies and I'm totally blanking on this.

Had Baby	# appointments
0	1
0	0
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
1	0
0	1
0	0
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
1	0
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
0	1
1	0

[grossal]

Hi @jbuszin,

I am not sure what the format should actually look like in the new column, but I managed to sum it up. 

Output:

Had Baby	#Sum_# appointments
0	14
1	26
2	14
3	0

What's happening:

1) I make transform the baby column to an increasing counter and let it go up everytime we see a 1.

2) Summarize it. Grouped by 'Had Baby' and Sum of appointments.

Can you show me an example of the expected output to further help you?

Workflow attached.

Best 

Alex

[AbhilashR · Accepted solution]

@jbuszin - attached is my attempt to compute the sum of appointment before each baby

EDIT - I have reattached a modified version of my solution to accommodate for multiple ID's in your data.

[jbuszin]

Sorry I'm not being clear. I'm trying to create "total appointments between babies". This would be by ID.

ID	Had Baby	# appointments	Appointments between babies
1	0	1	14
1	0	0	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	1	0	0
1	0	1	26
1	0	0	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	0	1	26
1	1	0	0
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	0	1	14
1	1	0	0

[jbuszin]

I have another scenario and was hoping to take your logic to figure out the new scenario but I'm not getting it. Instead of the variable being 0/1 it's now got three options (0, 1, null). Trying to stick to my example, we have doctor appointments and then no births (null) and birth of boy (0) and birth of girl (1). 

The data below shows 14 doctor appointments before the birth of a girl. After that birth, there was 1 doctor appointment before the birth of a boy. After that there were 25 appointments and then the birth of a girl. Then 14 appointments before the birth of another girl. Finally, 0 appointments and the birth of a boy.

In your very helpful solution you provided the number of appointments where there was a birth. I'm trying to capture the number of appointments before a boy being born and the number of appointments before a girl being born. The difference between this example and what I presented before that you did a solution for is before "birth" was all filled out, 0 or 1, depending on a birth. Now 0 means something and 1 means something and null exists, and it means something (no birth). Can you please provide guidance?

appointments	birth
1
1
1
1
1
1
1
1
1
1
1
1
1
1
0	1
1
0	0
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
0	1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
0	1
0	0

[AbhilashR · Accepted solution]

@jbuszin - does the attached solution work? look for the section named Latest Solution.

[jbuszin]

Thank you so much for your guidance. I'm not laying this out to you all correctly because I'm trying to use an analogy rather than what is in my actual data. Here it is:

I have this data where a row represents an activity. There are multiple people in the data and each person can have many activities. The data below is just for one person. I am trying to capture how much marketing activity happens between days between appointments, and I care whether the days between appointments are greater than 30 days or 30 days or less. 

The first appointment below happens 128 days after the appointment before (which is the second row, as you see "marketing activity" is a 0). I can figure out how to sum up the marketing activity where the days between appointments is more than 30 days, thanks to a lot of help from you already. That is why from what you provided (14, 26, 14 were all right). I need to sum up the marketing activity where the days between appointments is 30 days or less too.  I thought I could take your logic for the greater than 30 days and apply it to 30 or fewer days but I can't. 

The answer should be that, by person, marketing activity should sum to 1 in the first instance where days between appointments is 30 or fewer days (because if you look there's a single row of marketing activity between 128 and 12 in the days between appointments. Then, marketing activity should sum to 0 because in the next instance of 30 or fewer days between appointments it's been just 1 day (prior to that it had been 69 days). Then there is an appointment 7 days later, and before that 7 days later, there are 4 marketing activities and so the sum should be 4. Finally, there's an appointment a day later but there was also a marketing activity before that and so it should be 1. 

Complicated but not too complicated I hope! I feel like the solution is on the tip of my tongue but not quite there. I hope I have better explained this now.

Days_between_appointments	30+days	marketing activity	30+days_new
	0	1
	0	0
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
128	1	0	1
	0	1
12	0	0	0
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
85	1	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
	0	1
69	1	0	1
1	0	0	0
	0	1
	0	1
	0	1
	0	1
7	0	0	0
	0	1
1	0	0	0