Hi Everyone - I'm hoping to get some input on the design of a new set of analytics.
The information presented in the attached file shows a set of insurance policies that were written at some point in time, and then lapsed, but then a new policy is written again not soon after the lapse took place. This is a scheme that is not really good for the customer.
We currently use a bunch of sorts in excel to arrive at these situations and would like to flag/identify this data using qlikview. We would also like to be able to group the output by Agency and Agent to see who has the highest replacement rate.
I know this is unique to insurance, but there is churning anywhere accounts/sales are used. Any help expanding our knowledge in using QV would be appreciated.
As a possible start you can try something like the attached, but you are going to need to experiment and look at how your data works.
The key to this is sorting the data through the load, then using the peek function to look back at the prior row, so I've assumed here that you would consider a plan to replace another if the customer is the same, the plan type is the same & the IssueDate is after the LapseDate;
If(Peek(Name)=Name AND Peek([Plan Type])=[Plan Type] AND IssueDate>Peek(LapseDate),
Maybe then you could get a simple metric based on counting the REPLACES field (so below), or maybe you need to do something to link the dates more (maybe being replaced 'quickly' is Ok?). If OwnerCode is actually your customer identifier maybe that works better than Name?
If this helps happy to try to answer small specific questions on taking it forward, although I do have a day job :smileyhappy:.
Chris - thank you for this reply, sorry for the delay as I never saw any indication that my question had a response. there's also been a lull with this data as its foreign and I didn't have approval to work on it. Now I do. I will check this out as it looks as it gets me to where I want to be, and I can finally test with live data.
Thanks again for the response and learning opportunity!