7 Replies Latest reply: May 11, 2016 2:01 PM by Stefan Wühl RSS

    Contract status and renewal

    Jeff Robertz

      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 dataCalculated dimensions
      ContractIDS/NStartDateEndDateContract statusContract renewal status
      100101.01.201230.06.2012ExpiredRenewed
      101115.07.201230.06.2013ExpiredRenewed
      102102.07.201330.06.2016ActiveNot yet renewed
      103201.01.201230.06.2014ExpiredNot renewed
      104301.01.201130.06.2013ExpiredRenewed
      105301.07.201330.06.2016ActiveRenewed
      106301.07.201630.06.2018FutureNot yet renewed
      107401.07.201630.06.2018FutureNot yet renewed
      108501.01.201230.06.2012ExpiredRenewed
      109515.07.201230.06.2013ExpiredNot renewed
      110601.07.201330.06.2016ActiveRenewed
      111601.07.201630.06.2018FutureRenewed
      112601.07.201830.06.2020FutureNot yet renewed
        • Re: Contract status and renewal
          Stefan Wühl

          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;

            • Re: Contract status and renewal
              Jeff Robertz

              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!

               

                 

              ContractIDStartDateEndDateS/NContract statusContract renewal statusExpected
              10001.01.201230.06.20121ExpiredRenewed
              10115.07.201230.06.20131ExpiredRenewed
              10202.07.201330.06.20161ActiveNot yet renewed
              10301.01.201230.06.20142ExpiredNot renewed
              10401.01.201130.06.20133ExpiredRenewed
              10501.07.201330.06.20163ActiveRenewed
              10601.07.201630.06.20183FutureNot yet renewed
              10701.07.201630.06.20184FutureNot yet renewed
              11001.07.201330.06.201616ActiveRenewed
              11101.07.201630.06.201816FutureRenewed
              11201.07.201830.06.202016FutureNot yet renewed
              10801.01.201230.06.201255ExpiredRenewed
              10915.07.201230.06.201355ExpiredNot yet renewedNot renewed
              10001.01.201230.06.2012123ExpiredRenewed
              10115.07.201230.06.2013123ExpiredRenewed
              10202.07.201330.06.2016123ActiveNot yet renewed
              10202.07.201330.06.2016124ActiveNot renewedNot yet renewed
              10202.07.201330.06.2016125ActiveNot renewedNot yet renewed
                • Re: Contract status and renewal
                  Stefan Wühl

                  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;

                    • Re: Contract status and renewal
                      Jeff Robertz

                      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,

                      • Re: Contract status and renewal
                        Parth Shah

                        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_idstart_dateend_dateserial_numberstatusrenewal_statusexpected result of renewal_status
                        1
                        12/23/201512/22/2018101ActiveNot yet renewed Renewed
                        212/23/201812/22/2020101FutureRenewed Not yet renewed
                        312/23/201512/22/2018102ActiveNot yet renewed Renewed
                        412/23/201812/22/2020102FutureRenewed Not yet renewed
                        51/1/201512/31/2017103ActiveNot yet renewed Renewed
                        61/1/201812/31/2019103FutureRenewed Not yet renewed
                        71/1/201512/31/2017104ActiveNot yet renewed Renewed
                        81/1/201812/31/2019104FutureRenewedNot yet renewed

                         

                        Scenario 2:

                        contract_idstart_dateend_dateserial_numberstatusrenewal_statusexpected result of renewal_status
                        2010/9/201410/8/2015115ExpiredRenewed
                        2110/9/20159/30/2016115ActiveNot yet renewed
                        222/1/20152/1/2016116ExpiredNot renewed Renewed
                        232/1/20169/30/2016116ActiveRenewedNot yet renewed

                         

                        I'd greatly appreciate any help you can provide.

                         

                        Thank you,

                        Parth Shah