Difference between multiple variable dates along multiple rows

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

DateDifference.yxmd

Date Time

Data Investigation

Accepted answers

echuong1

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.

DateDifference.yxmd

TheOC

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!

ThomasT.yxmd

ThomasT.yxmc

All comments

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.

ThomasT.yxzp

echuong1

Is this what you're looking for?

DateDifference.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703