As with everything in Alteryx, multiple approaches are possible. Here's mine.
- First, sort by member number and coverage period (both ascending).
- Next, insert multi-record tool, title field STOP SERVICE. Format as date. Formula is:
IF [MEMBER] = [Row-1:MEMBER] and DateTimeDiff([COVERAGE PERIOD],[Row-1:COVERAGE PERIOD],"months") = 1
then DateTimeadd([COVERAGE PERIOD],1,"months")
else null() endif - Next, a second multi-record tool, title field START SERVICE. Format as date. Formula is:
IF [MEMBER] = [Row-1:MEMBER]
and DateTimeDiff([COVERAGE PERIOD],[Row-1:COVERAGE PERIOD],"months") = 1
and !isnull([STOP SERVICE]) then [Row-1:START SERVICE]
else [COVERAGE PERIOD] endif - Next, a summarize tool. Group by MEMBER, then START SERVICE, and get the MAX of STOP SERVICE. Title this field STOP SERVICE within the summarize tool.
- Finally, add a formula tool, have it look at STOP SERVICE, and apply this formula:
if isnull([STOP SERVICE]) then DateTimeadd([START SERVICE],1,"months") else [STOP SERVICE] ENDIF
Test it out, examine the outputs at various steps, make sure it's doing what you want. I tested it with success on your dataset, then I expanded your dataset, adding other members, and other months, all out of sequence. Output seemed correct to me.
