Hello. In my workflow, I have ranked the DATE field grouped by ID using Multi-Row Formula. I need to populate the QUALIFIED field with "Yes" if the next ranked date is over 30 days from the previous qualified date. Rank 1 will always be qualified. Please see example below. Data: IDDATERANKXXXXX9-Jan-20191XXXXX29-May-20192XXXXX26-Jun-20193XXXXX21-Aug-20194XXXXX16-Oct-20195XXXXX20-Nov-20196XXXXX21-Nov-20197yyyyy6-Apr-20191yyyyy30-Apr-20192yyyyy1-May-20193yyyyy1-Jun-20194yyyyy4-Jun-20195yyyyy1-Jul-20196ZZZZZ2-Aug-20191ZZZZZ13-Aug-20192ZZZZZ6-Oct-20193 Result: IDDATERANKQUALIFIEDXXXXX9-Jan-20191YesXXXXX29-May-20192YesXXXXX26-Jun-20193 XXXXX21-Aug-20194YesXXXXX16-Oct-20195YesXXXXX20-Nov-20196YesXXXXX21-Nov-20197 yyyyy6-Apr-20191Yesyyyyy30-Apr-20192 yyyyy1-May-20193 yyyyy1-Jun-20194Yesyyyyy4-Jun-20195 yyyyy1-Jul-20196YesZZZZZ2-Aug-20191YesZZZZZ13-Aug-20192 ZZZZZ6-Oct-20193Yes Thank you.

Looping through rows

Hello.

In my workflow, I have ranked the DATE field grouped by ID using Multi-Row Formula. I need to populate the QUALIFIED field with "Yes" if the next ranked date is over 30 days from the previous qualified date. Rank 1 will always be qualified. Please see example below.

Data:

ID	DATE	RANK
XXXXX	9-Jan-2019	1
XXXXX	29-May-2019	2
XXXXX	26-Jun-2019	3
XXXXX	21-Aug-2019	4
XXXXX	16-Oct-2019	5
XXXXX	20-Nov-2019	6
XXXXX	21-Nov-2019	7
yyyyy	6-Apr-2019	1
yyyyy	30-Apr-2019	2
yyyyy	1-May-2019	3
yyyyy	1-Jun-2019	4
yyyyy	4-Jun-2019	5
yyyyy	1-Jul-2019	6
ZZZZZ	2-Aug-2019	1
ZZZZZ	13-Aug-2019	2
ZZZZZ	6-Oct-2019	3

Result:

ID	DATE	RANK	QUALIFIED
XXXXX	9-Jan-2019	1	Yes
XXXXX	29-May-2019	2	Yes
XXXXX	26-Jun-2019	3
XXXXX	21-Aug-2019	4	Yes
XXXXX	16-Oct-2019	5	Yes
XXXXX	20-Nov-2019	6	Yes
XXXXX	21-Nov-2019	7
yyyyy	6-Apr-2019	1	Yes
yyyyy	30-Apr-2019	2
yyyyy	1-May-2019	3
yyyyy	1-Jun-2019	4	Yes
yyyyy	4-Jun-2019	5
yyyyy	1-Jul-2019	6	Yes
ZZZZZ	2-Aug-2019	1	Yes
ZZZZZ	13-Aug-2019	2
ZZZZZ	6-Oct-2019	3	Yes

Thank you.

Best Practices

Accepted answers

fmvizcaino

Hi @jpbonilla ,

Sorry for taking so long to answer.

Here is a new version of the workflow.

Take a look and let me know if this works for you.

Best,

Fernando Vizcaino

2020-03-09 Looping through rows.yxmd

All comments

fmvizcaino

Hi @jpbonilla ,

I've used a multi-row tool to calculate the month difference between dates from the same ID (group by ID) and then a formula to fill the qualified column.

Let me know if this works for you.

Best,

Fernando Vizcaino

2020-02-26 Looping through rows.yxmd

jpbonilla

Thank you for your help @fmvizcaino ! I changed the date difference from Month to Day, and tried to calculate the days from previous qualified date (not previous date) in order to get the next qualified date. Would appreciate your help on the part where Date is at least 30 days from previous qualified date. Please see below. Thank you.

ID	RANK	DateTime_Out	Day Difference	Day Difference (Correct)	Qualified	Qualified (Correct)
XXXXX	1	1/9/2019			Yes
XXXXX	2	5/29/2019	140		Yes
XXXXX	3	6/26/2019	28
XXXXX	4	8/21/2019	56	84	Yes
XXXXX	5	10/16/2019	56		Yes
XXXXX	6	11/20/2019	35		Yes
XXXXX	7	11/21/2019	1
ZZZZZ	1	8/2/2019			Yes
ZZZZZ	2	8/13/2019	11
ZZZZZ	3	10/6/2019	54	65	Yes
yyyyy	1	4/6/2019			Yes
yyyyy	2	4/30/2019	24
yyyyy	3	5/1/2019	1
yyyyy	4	6/1/2019	31		Yes
yyyyy	5	6/4/2019	3
yyyyy	6	7/1/2019	27	30		Yes

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703