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.
Solved! Go to Solution.
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
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 |
Hi @jpbonilla ,
Solution attached. I needed to insert two more multi-row tools to make that happen.
Best,
Fernando Vizcaino
Thank you again, @fmvizcaino . I apologize I wasn't clear in my reply. I didn't mean to add more columns. Hoping your help for the expected result attached considering the calculation for the DayDifference will be from the last Qualified date as originally requested.
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