Trying to find the Date Difference using the Multi-Row Formula

I am struggling to calculate the dates of service between two dates. While I believe there is something simple I am missing, it evades my comprehension. I am using the multi-row formula to attempt to calculate the datetime differences between two dates adjacent in the same column. My results is always null, and I cannot understand why. I even tried to just make a simple calculation for an end date using the formula function and then calculating the difference using the multi-formula tool - still results in null. Any help?

Workflow Example:

workflow example date diff.png

Workflow Input:

sample input.png

Workflow Results:

workflow results.png

Output

Date Time

Accepted answers

TimN

How about this...

DateDiff.yxmd

cjaneczko

Check the configuration? Seems to work ok with the below config.

All comments

TimN

How about this...

DateDiff.yxmd

cjaneczko

Check the configuration? Seems to work ok with the below config.

TimN

Looks exactly the same as yours but worked for me...

jeprime

The issue was that I had marked the Group By marked on the variable DateTime_Out .

issue problem.png

When I unmarked it, it worked perfectly. See corrected example:

issue solution.png

and corrected results:

workflow solution results.png

jeprime

Thanks - I noticed that in your configuration, you did not select a group by from the table. I unselected it from mine and it worked perfectly. Much appreciated!

jeprime

Your example also proved that one simple error can mess up the entire thing, AND that collaboration in developing is pertinent to good programming. Thanks so much.

jeprime

Now that the initial problem has been solved, I wonder if anyone could provide an answer as to when the GROUP BY portion would be best utilized and for what purpose?

TimN

Found this, will read too, lol

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-use-group-by-in-Multi-row-formula/td-p/607084

LiliR

Hi, I have an issue related to the multi row formula. In my case I'm grouping two records/rows by the name of a client and I'm using the difference formula between dates. In the majority of cases I have two records for the same client and I can find the days between records, but I get a null value when there are no prior records. What can I do when there is not a prior record of that client and instead of getting null, I want to get zero days? Thanks!

I would use a data cleansing tool after the multi-row formula. It would change the nulls to zeros - see the attached image.

data cleansing tool.png

LiliR

Thank you. It worked!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335