Hi all,
I want to create a new column called "PlanEndDate" in this workflow. Below are my fields. the EEPID field is unique, and repeats for every time a plan is changed. The PlanEffectiveDateHistory Field is the "start date" of that plan, but i need to calculate the end date. I cant get my if statment to work correctly in my MultiRow tool. Here is what I want to accomplish in that tool
IF ([Row+1:EEPID] = [EEPID] then Row+1 PlanEffeftiveDateHistory minus one day. If the EEPID row is the last one then it would populate with the last day of the current month or something that would indicate that the plan is active and has not ended. This would be a date field, so putting "current" in the field probably wouldn't work
for example records 1-4 is the same person, and here is the desired outcome for the new field
| Record | EEPID | EmployeeEnrollmentDate | PlanEffDateHistory | PlanEndDate |
| 1 | 01005.087.M | 2006-04-01 | 2006-04-01 | 2009-03-31 |
| 2 | 01005.087.M | 2006-04-01 | 2009-04-01 | 2014-11-30 |
| 3 | 01005.087.M | 2006-04-01 | 2014-12-01 | 2017-11-30 |
| 4 | 01005.087.M | 2006-04-01 | 2017-12-01 | current plan or some kind of date that could always indicate that its a current plan. maybe 2222-01-01 |
Here is my sample data
