Contract status and renewal

[agaetisproject]

Dear all,

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
100	1	01.01.2012	30.06.2012	Expired	Renewed
101	1	15.07.2012	30.06.2013	Expired	Renewed
102	1	02.07.2013	30.06.2016	Active	Not yet renewed
103	2	01.01.2012	30.06.2014	Expired	Not renewed
104	3	01.01.2011	30.06.2013	Expired	Renewed
105	3	01.07.2013	30.06.2016	Active	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
108	5	01.01.2012	30.06.2012	Expired	Renewed
109	5	15.07.2012	30.06.2013	Expired	Not renewed
110	6	01.07.2013	30.06.2016	Active	Renewed
111	6	01.07.2016	30.06.2018	Future	Renewed
112	6	01.07.2018	30.06.2020	Future	Not yet renewed

[swuehl]

I just worked on the renewal status and used the Contract status from your excel file (since it seems to be easy to implement):

INPUT:

LOAD ContractID,

     [S/N],

     StartDate,

     EndDate,

     [Contract status],

     [Contract status] as CS,

     [Contract renewal status] as Check2

FROM

[Contract example.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1))

));

LEFT JOIN (INPUT)

LOAD [S/N],

  count([S/N]) as CountSN

RESIDENT INPUT

GROUP BY [S/N];

LOAD ContractID,

  [S/N],

  StartDate,

  EndDate,

  [Contract status],

  if(peek([S/N]) <> [S/N], if(CountSN>1 or CS='Future', 'Not yet renewed','Not renewed'),'Renewed') as [Contract renewal status]

// Check2,

// CountSN

Resident INPUT

ORDER BY [S/N], ContractID desc;

DROP TABLE INPUT;

[agaetisproject]

Million thanks Swuehl! This is extremely helpful.

I am really impressed by both the short lead time and your excellent answer.

I tested it on my end tonight and it works almost perfectly.

Nothing wrong done on your end, just that, as always, real data are slightly more complex than the data provided in my original sample and I noticed two edge cases:

- Edge case 1: there are often many S/N under one contract and in that case, the 'Renewed' or 'Not yet renewed' logic does not provide the expected results

- Edge case 2: when a machine had two (or more) contract that expired in the past, the status turns to 'Not yet renewed' for the second contract while I expected a 'Not renewed'

Could you kindly indicate me how to tweak the last part of your script to account for such edge cases?

Again, thanks for your very precious help!

ContractID	StartDate	EndDate	S/N	Contract status	Contract renewal status	Expected
100	01.01.2012	30.06.2012	1	Expired	Renewed
101	15.07.2012	30.06.2013	1	Expired	Renewed
102	02.07.2013	30.06.2016	1	Active	Not yet renewed
103	01.01.2012	30.06.2014	2	Expired	Not renewed
104	01.01.2011	30.06.2013	3	Expired	Renewed
105	01.07.2013	30.06.2016	3	Active	Renewed
106	01.07.2016	30.06.2018	3	Future	Not yet renewed
107	01.07.2016	30.06.2018	4	Future	Not yet renewed
110	01.07.2013	30.06.2016	16	Active	Renewed
111	01.07.2016	30.06.2018	16	Future	Renewed
112	01.07.2018	30.06.2020	16	Future	Not yet renewed
108	01.01.2012	30.06.2012	55	Expired	Renewed
109	15.07.2012	30.06.2013	55	Expired	Not yet renewed	Not renewed
100	01.01.2012	30.06.2012	123	Expired	Renewed
101	15.07.2012	30.06.2013	123	Expired	Renewed
102	02.07.2013	30.06.2016	123	Active	Not yet renewed
102	02.07.2013	30.06.2016	124	Active	Not renewed	Not yet renewed
102	02.07.2013	30.06.2016	125	Active	Not renewed	Not yet renewed

[swuehl · Accepted solution]

I think I've overcomplicated things before, you don't need the aggregation and join step.

LOAD ContractID,

    StartDate,

    EndDate,

    [S/N],

    [Contract status]

//    [Contract renewal status] as Check2,

//    Expected

FROM

[https://community.qlik.com/thread/172582]

(html, codepage is 1252, embedded labels, table is @2);

LOAD ContractID,

  [S/N],

  StartDate,

  EndDate,

  [Contract status],

  if(peek([S/N]) <> [S/N], if([Contract status]<>'Expired', 'Not yet renewed','Not renewed'),'Renewed') as [Contract renewal status]

//  Expected,

//  Check2

Resident INPUT

ORDER BY [S/N], ContractID desc;

DROP TABLE INPUT;

[agaetisproject]

Excellent, thank you Swuehl for your precious help and prompt answers.

You really made my day and learnt me how to use the very useful peek function!

Note, I sort the table on S/N, then EndDate as the source file had some inconsistencies in the attribution of ContractID. Minor adjustment for those out there that may use this answer.

Cheers,

[#6]

Thank you Jeff and Swuehl. This is really helpful. Can anyone please help me calculate renewal rate KPI based on this? Greatly appreciate the work of posting this.

Thank you,

Parth

[#7]

Hi Swuehl,

Thank you for the solution. I am also trying to create this new field for Contract Renewal status. The above mentioned solution worked perfectly for most cases. Because the data we have for the contracts; only places I saw different result than expected are below:

Scenario 1:

contract_id	start_date	end_date	serial_number	status	renewal_status	expected result of renewal_status
1	12/23/2015	12/22/2018	101	Active	Not yet renewed	Renewed
2	12/23/2018	12/22/2020	101	Future	Renewed	Not yet renewed
3	12/23/2015	12/22/2018	102	Active	Not yet renewed	Renewed
4	12/23/2018	12/22/2020	102	Future	Renewed	Not yet renewed
5	1/1/2015	12/31/2017	103	Active	Not yet renewed	Renewed
6	1/1/2018	12/31/2019	103	Future	Renewed	Not yet renewed
7	1/1/2015	12/31/2017	104	Active	Not yet renewed	Renewed
8	1/1/2018	12/31/2019	104	Future	Renewed	Not yet renewed

Scenario 2:

contract_id	start_date	end_date	serial_number	status	renewal_status	expected result of renewal_status
20	10/9/2014	10/8/2015	115	Expired	Renewed
21	10/9/2015	9/30/2016	115	Active	Not yet renewed
22	2/1/2015	2/1/2016	116	Expired	Not renewed	Renewed
23	2/1/2016	9/30/2016	116	Active	Renewed	Not yet renewed

I'd greatly appreciate any help you can provide.

Thank you,

Parth Shah

[swuehl]

I would suggest that you create a new thread for your issue.

- Add a test script with some sample records

- Define which rules apply to your renewal status