Finding Replacement Insurance Policies

[George_Fischetti]

Good morning folks - I'm new to Alteryx and trying to learn the terminology/process of how the tool works as related to some other tools I've used.  This community is awesome so I thought I'd ask for some help.

I have a dataset of life insurance policies written in the last 2 years and I'm trying to detect when a customer lapses or surrenders a policy and immediately buys another policy on the date of the cancellation or a few days later.   I have not been able to find this topic when searching, but doubting I'm the only person who has tried this.

I'm not familiar with all the techniques to iterate through the data and can use some guidance (I think that's the right term).  Which tools would handle this the best?  Any help is appreciated!

Some Sample data is below, Customer AA and CC are the ones I'm interested in finding.

Thanks, George  

PolicyNum	Customer	IssueDate	PolicyStatus	PlanCode	TerminationDate
1	AA	12/30/2019	Lapsed	LIF 10	12/30/2020
2	BB	11/14/2016	Active	AH 12
3	BB	12/1/2015	Active	AH 12
4	AA	1/3/2021	Active	LIF 20
5	CC	4/18/2016	Surrendered	LIF 10	4/18/2016
6	CC	4/18/2016	Active	LIF 20

[DawnDuong]

hi @George_Fischetti 

I would suggest the following schematic:

1) Sort your data by customer name, issued date, termination date, and status.

2) use a Multi-row formula to calculate the number of days between lapsed/surrendered and the next immediate active policy. Note the use of the Group by option with the Multi-row tool.

I attached here a sample workflow. Hope it helps you.

Dawn.

[George_Fischetti]

Thank you Dawn - I will try this as soon as my new dataset arrives.  Appreciate your input.

[atcodedog05]

Hi @George_Fischetti 

Here is my take on it which is slightly different.

Workflow:

1. Using Multi-field formula to convert date to alteryx date format.

2. Using Multi-row formula to check for lapsed and surrendered policy is there a new active policy for the same customer.

Hope this helps : )

Hope this helps : )