Multi Row Formula to Count Days between Dates in Criteria is Met

I am trying to return two columns in the simple attached workflow. The first likely needs a multi-row formula, which I'm not great at. I need to count the days between the two dates (each row) if the name of the client isn't new. If it is new, it needs to fill in a blank/null. The second column/formula needs to count the number of dates/rows that fall within three days of the date in the row when the name again matches. The output would look like this...

Example.yxmd

Tips and Tricks

Accepted answers

BenMoss

You are right, the first piece is fairly easy to resolve with the multi-row formula tool.

The second part is slightly more complex but still manageable. You can simply use the datetimeadd() function and the generate rows tool to identify the days that would need to match for it to be included in the count, and then join both on that lookupdate with the original date to identify marks which fell on that date, then use a summerize to count.

I've attached a sample workflow.

Ben

Solution.yxmd

All comments

BenMoss

You are right, the first piece is fairly easy to resolve with the multi-row formula tool.

I've attached a sample workflow.

Ben

Solution.yxmd

Claje

Ben's solution will definitely work.

Here's an alternative method, where instead of generating future dates and comparing, we compare all dates for each person.

Which one of these is faster/better probably depends on how many records you are dealing with and how many times an individual client shows up in the data (in this example they both run in .4 seconds on my machine), but I want to emphasize that both work.

Claje_DtCompare.yxmd

melaniekim1121

Hi BenMoss - I know this post is extremely old, but I used your solution to help me solve an issue with my workflow. Although it fixed my issue, I am trying to really understand the individual steps of the workflow.

Could you explain why you have a filter tool for: [Record ID] <= [Right_RecordID] ? I'm having a hard time comprehending this step.

Thank you!

Melanie

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703