multi-row formula

Question

My data looks like this:

Employee     Date1               Date2

emp 1 - 1      2022-08-01     2022-08-09

emp 1 - 2      2022-08-22     2022-08-26

emp 1 - 3      2022-11-08     2022-11-09

can someone help with a multi-row formuls that would help me determine:

is date1 of row 2  2022-08-22

more than 5 days from date 2 of row 1 (which in this example is 2022-08-09

and again, is date 1 of row 3 2022-11-08 more than 5 days since row2, date 2 which in this example is 2022-08-26

Thank you in advance

example.multi-row.xlsx

grazitti_sapna · Answer

Hi @bh1789 
Hope it works for you. kindly Check.
Thanks

five.yxzp

rzdodson · Answer

@bh1789 have a couple of solutions for you: standard workflow, and analytic app (workflow attached).

What I did was assign a unique record ID (RecordID tool), transposed the data to decrease the number of Multi-Row formula tools used. The first Multi-Row tool used determines if the dates are more than 5 days from each other in order. Then the second Multi-Row tool (Multi-Row (16)) returns "Yes' or "No" if the difference in days is more than 5.

The second solution utilizes an Alteryx Analytic App - my personal go-to so I can continuously augment the testing parameters of the workflow without having to change the number of tools on the canvas. You can access the Analytic App interface by clicking on the wand icon next to >Run in the upper right-hand corner.

The Analytic App utilizes the same logic with a slightly different setup.

First, I use a Numeric Up Down tool (Interface tab) to create some basic parameters for the data analysis.

Then, I set up two Action tools. The first action tool changes the number in the "More than ..." field so we can dynamically see how many days we are testing for.

Then, in my second Action tool, I update the expression I previously used to specify the new number of days I am testing for. This is where I can toggle the number of days I am testing for without changing the workflow.

Below is the output based on the selection I have made in the app interface.

If this helps, please make sure to accept as a solution so it can help users on their future workflows. :)

3 - Find Days Diff Multi-Row.yxzp

ArtApa · Answer

Hi @bh1789 - Here is a formula: "if datetimediff([Date2],[Row-1:Date1],"days")>5 then "more than 5" else "less or equal 5" endif"

Date1Date2AA.yxmd