Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

Highlighted
7 - Meteor

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...

Capture3.PNG

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
ACE Emeritus
ACE Emeritus

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.

Labels