I need some help from the community on a question concerning contract status and renewal.
I need to compute the number of active, expired and future contract, say for an insurer.
The original data contains:
- the ContractID
- the S/N (typically an ID for the person or object insured)
- the StartDate of the contract
- the EndDate of the contract.
See table below for a representative sample.
Could you help me compute the Calculated Dimensions in the script as follows?
- Contract status (this one is pretty simple as it can be computed on a row per row basis)
- Contract renewal status is much more tricky as it requires combining multiple rows and columns)
As you can see, the rules are pretty simple:
- a contract that is Expired/Active/Future should be flagged as Renewed when it is followed by another contract for the same S/N.
- a contract that is Expired should be flagged as Not renewed when there is no contract for the same S/N.
- a contract that is Active/Future should be flagged as Not yet Renewed when it is not yet followed by another contract for the same S/N.
Please note that there are some gaps between the start date/end date in some records so we cannot rely on successive checks there.
Cherry on the cake, compute the contract renewal rate KPI :-)
I browsed many similar post but could not find an answer while I am sure many people out there face the exact same issue.
Can you help?
|Original data||Calculated dimensions|
|ContractID||S/N||StartDate||EndDate||Contract status||Contract renewal status|
|102||1||02.07.2013||30.06.2016||Active||Not yet renewed|
|106||3||01.07.2016||30.06.2018||Future||Not yet renewed|
|107||4||01.07.2016||30.06.2018||Future||Not yet renewed|
|112||6||01.07.2018||30.06.2020||Future||Not yet renewed|
Contract example.xlsx 9.6 K