Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

multi-row formula

bh1789
8 - Asteroid

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

3 REPLIES 3
ArtApa
Alteryx
Alteryx

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

 

 

rzdodson
12 - Quasar

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

1 - Solution.png

 

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.

2 - Numeric UpDown.png

 

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.

2 - Update Field.png

 

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.

2 - Update Tsting Value.png

 

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

2 - App Results.png

 

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

grazitti_sapna
17 - Castor

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

Sapna Gupta
Labels
Top Solution Authors