Difference between multiple variable dates along multiple rows

[ThomasT]

Hi all, 

my first post, so far i have found all the answers, but couldn't find the solution to the problem i'm working on right now. 

I have a data set with multiple rows, each row has a unique identifier (VIN). Along the columns i have a date range from 2017 - present split by month. 

Now in each row i have a different instance of an event or multiple events. The goal is to calculate the time duration between each even for each row. I got as far as summarizing and cutting out blanks...but after 3hrs of playing I feel like this is only possible with a massive IF function or a macro. 

Before i go down that path i though i might give the community a try.

Thanks,

Thomas

[TheOC]

Hi @ThomasT 

Just having a quick look at this now, what is your goal for an output? can I have a quick example file/table?

And is it between every event, or the first and last for each row?

Cheers!

[TheOC]

hi @ThomasT 

I gave this a go, I hope its somewhat close to what you were expecting, but if not give me a reply/pm in the morning and i'll give it another go.

[echuong1 · Accepted solution]

Is this what you're looking for?

You can essentially pivot your data and then filter out any nulls to remove values without dates/events. From there, you'd need to convert your values to proper date format (with the datetime tool). You can then use the multi-row formula to do the difference calculation.

[ThomasT]

EDIT:

I haven't refreshed since i saw your post and just realized the solution was posted a short time later. Thanks all! Amazing community.

Thomas

//

Thanks @TheOC 

I unfortunately can't open your file since i'm still on Alteryx 2018.3. I can open workflows created on newer versions but the package doesn't work unfortunately. 

In the data set, you will find multiple events between 2017 till now in every row. The goal is to calculate the time difference between each event. 

In my previous attempt I put all the different dates from one row into a column per VIN (see picture attached). So now you can see every VIN has multiple events and those events differ for each VIN. My goal is to calculate the difference between the events for each row. 

I hope that makes sense.

[ThomasT]

Thanks @echuong1 

Really impressive, i haven't used the multi row formula before and definitely have to work my way into that as that left me well confused how this automatically leaves a Null between new rows. 

I have accepted the solution, even though I'm having troubles applying this to my whole 34000 data points. For some reason the 'Group By' within the multirow formula doesn't group well. It works by using the Summarize tool, but somehow not through the multirow formula. 

So I summarized beforehand and then tried to do it without the 'Group By' and left all the other things in the multirow formula as you created it and that seems to work except that it calculates negative values instead of 'Null' when transitioning from one group to the next. 

Thanks,

Thomas

[TheOC · Accepted solution]

Hi @ThomasT 

Really sorry to hear you were unable to open it, I cant quite remember what part of it fails on that side, but hopefully this should work for you:

I have attached the macro and the workflow, you will have to import the macro, and then have both inputs of the macro as your data. 
At the bottom of the questions tab of the configuration panel, you will have to set the control parameter as Row Labels:

And then hopefully this works for you!

If not, again, please give me a shout!

[echuong1]

In the multi-row, ensure that you have your unique ID as a group by field. Also, be sure to set the values for rows that don't exist to 0. 

As shown in your photo, it's trying to do a continuous multirow down the entire table. You essentially want to create "subset tables" of your data to process each group of records.

[ThomasT]

Thanks @echuong1

I found the problem and it was simply that the grouping automatically sorted A-Z and a lot of those values in the first few hundred rows only had only 1 occurrence and i freaked out because everything was a 'Null'. All good now. I even added another multirow formula to create 'Event Numbers' that I cross-tabed back to columns, so now for each Row Label I get 5 columns with the days between each respective event, cool tool thanks for sharing the solution!

Cheers,

Thomas