6 Replies Latest reply: Apr 9, 2014 4:27 PM by michael taylor RSS

    In-Force Policy Reporting

    Darin DeBoer

      Here's a question for the community.  I am developing an insurance dashboard and have been able to accomplish the various representations I need for everything associated with explicit policy effective and expiration dates, e.g., how many policies were written new in October 2012.  I can't figure out a way to easily handle in-force reporting.  Example: 6-month Policy X written in October 2012, should be counted as "in-force" each month from October 2012 thru March 2013. 

       

      I'm trying to show this in a pivot chart using YEARMONTH as a dimension.   My expression is a count of POLICYID.  The other variables available to me are EFFECTIVEDATE and EXPIRATIONDATE.  If I were to select a range of YEARMONTH values from my list box that included the months 2012-10 thru 2013-03, I should see Policy X counted each month it was in force. 

       

      I received some direction that set analysis would be the way to go but haven't been able to get it to work.  Other direction was to create fact tables in the database or develop much more convoluted load logic.  I'd like to avoid that if at all possible.  In-force reporting is a pretty common concept in the insurance world, so I'm hoping there is a simple way to address my reporting needs.

       

      I look forward to hearing back from the community!

       

      =========================================================================

       

       

      I worked with Robyn Danielsson, Harvey Johal, and Josh Good from QV with different approaches to this problem (as well as other challenges I had
      creating my first document).  Josh was able to solve the in-force problem using the logic shown below. 

       

      In essence, I ended up with a connection between the policy information (Effective_MonthStart and Expiration Month for IntervalMatch) with every
      corresponding entry in the In-Force calendar table (thru Inforce Date). 

       

      With this in place, I can select on an Inforce YearMonth from a list box, and get back to the appropriate policies.  My pivot chart uses AGY_NAME and Inforce YearMonth as dimensions and Sum(EST_POLICY_PREM)and COUNT(POLICY_ID) as expressions.

       

      //   Load Policy information.  Note the extra fields tied to effective and expiration dates to be used later in the interval match.

      Policies:
      LOAD "RENEWAL_POLICY_FLAG",
          
      Date(EFFECTIVE_DATE) as [Effective Date],
          
      Date(MonthStart(EFFECTIVE_DATE)) as [Effective_MonthStart]
          
      Date(EXPIRATION_DATE) as [Expiration Date],
          
      Date(MonthEnd(EXPIRATION_DATE, -1)) as [Expiration Month for IntervalMatch]
          
      IF("RENEWAL_POLICY_FLAG"='Y','Renewal',IF("RENEWAL_POLICY_FLAG"='N','New Business')) as POLTYPE,
          
      "POLICY_ID",
          
      "GOVERNING_CLASS_CODE_ID" AS CLASS_CODE_ID;
      SQL SELECT
      "RENEWAL_POLICY_FLAG",
                 "EFFECTIVE_DATE",
                 "EXPIRATION_DATE",
                 "POLICY_ID",
                 "GOVERNING_CLASS_CODE_ID"
      FROM POLICY
      where company_no=100;

       

      //   Create a calendar table that has every date between the first effective date and the last expiration date.  Note all the extra date-related fields that are created and allow for any number of different/deeper dives into the data.

      QuartersMap:
      MAPPING LOAD
      rowno() as [InForce Month],
      'Q' &
      Ceil (rowno()/3) as [InForce Quarter]
      AUTOGENERATE (12);

      Temp:
      Load
                    
      min([Effective Date]) as minDate,
                    
      max([Expiration Date])as maxDate
      Resident Policies;

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
      DROP Table Temp;

      TempCalendar:
      LOAD
                    
      $(varMinDate) + Iterno()-1 As Num,
                    
      Date($(varMinDate) + IterNo() - 1) as TempDate
                    
      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

      InForceCalendar:
      Load
                    
      TempDate AS [InForce Date],
                    
      week(TempDate) As [InForce Week],
                    
      Year(TempDate) As [InForce Year],
                    
      Month(TempDate) As [InForce Month],
                    
      Day(TempDate) As [InForce Day],
                    
      YeartoDate(TempDate)*-1 as [InForce CurYTDFlag],
                    
      YeartoDate(TempDate,-1)*-1 as [InForce LastYTDFlag,
                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as [InForce RC12]
      ,
                    
      date(monthstart(TempDate), 'MMM-YYYY') as [InForce MonthYear],
                    
      date(monthstart(TempDate), 'YYYY-MM') as [InForce YearMonth],
                    
      ApplyMap('QuartersMap', month(TempDate), Null()) as [InForce Quarter],
                    
      Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [InForce WeekYear],
                    
      WeekDay(TempDate) as [InForce WeekDay]
      Resident TempCalendar
      Order By TempDate ASC;

      Drop Table TempCalendar;

       

      //   Connect the InForceCalendar and the Policy tables using the IntervalMatch function


      IntervalMatchTable:
      INTERVALMATCH([InForce Date])
      LOAD DISTINCT
            
      [Effective_MonthStart],
            
      [Expiration Month for IntervalMatch]
      RESIDENT
             Policies;

       

       

       

       

       

      Message was edited by: dldeboer

        • Re: In-Force Policy Reporting
          Jonathan Poole

          I've implemented this in a sample once before.  In the sampe, the source data listed a single policy on each row and apart from the policy identifier field, the table had a 'expiration date' and an 'effective date' ... so 2 dates in the data source.  (there were actually more, lots of dates in the underwriting process but lets say just 2 for this example)

           

          In the UI i created a calander object which set a date value stored in a variable.. where the variable name was  "vAsOfDate". Next to the calendar object i put a button with the text <Apply>.  I added 2 actions to the button. Both actions were 'select in field' actions.

           

          The first action select the 'Expiration Date' field and for the search string i entered

           

          =$(vExpirationRange)

           

          ...where vExpirationRange is another variable with the definition: '>' & vAsOfDate

           

          The second action selected the 'Effective Date' field and for the search string i entered

           

          =$(vEffectiveRange)  

           

          ...where vEffectiveRange is another variable with the definition: '<=' & vAsOfDate

           

          In short, when a user hit the apply , it selected a range of policies with an expiration date greater than then the 'as of date' and filtered that set down to just the policies that had an effective date less than or equal to the 'as of date'.   Then you could just do a count distinct of the policy IDs to figure out how many policies were 'in force' as of the date the user enters.

           

          Note that 'count distincts' are not the most efficient expressions in qlikview. better to so a sum of rows from a policy dimension table... but that is another issue.

           

          Anyways i hope this helps, and hopefully you are able to map your 2 dates to the dates i created in this example.

          • Re: In-Force Policy Reporting
            michael taylor

            I'm just trying to apply this myself.  Is it meant to create 1 row per date per policy in the interval table? If not then I think aim going wrong somewhere!  Especially as I have 20m policies spanning the last 20 years.

              • Re: In-Force Policy Reporting
                Darin DeBoer

                The interval match associates each date from the InForceCalendar to the corresponding Policies rows when InForce Date falls between Effective_MonthStart and Expiration Month for IntervalMatch.  For example, if the latter dates are Jan 2013 and Dec 2013, each and every day between 1/1/2013 and 11/30/2013 in the InForceCalendar are associated with that row in the Policies table.

                 

                (Note that days in December are purposely omitted with our definition of in-force, which says the policy is in-force for any given month only if it is in-force on the last day of that month.  The -1 in the Policies Load statement moves the true expiration date back one month to use in the interval match.)

                 

                I then use dates from the InForceCalendar in my List Box objects.  Regardless if I pick day, week, month, year, quarter, etc. from the various date permutations created in the InForceCalendar Load logic, I can determine whether a given policy was in-force on that selected calendar unit.

                 

                My need was to count in-force policies or summarize in-force premium month-over-month in a pivot chart.  The amount of data that I’m using doesn’t come close to the number of policies and years you have.  I can’t speak to what the load time or resulting QV file size would be, but I hope my email makes things a little more clear.

                 

                Let me know if I can provide more information.

                 

                Darin

                  • Re: In-Force Policy Reporting
                    michael taylor

                    hi Darin, thanks for your prompt reply.

                     

                    I understand now exactly what you did and it sounds like the its the same thing I am trying to get to.  I think however as you say the data volumes would be too large to create one record per policy per day in-force.  I had also not changed the high end date we had ('31/12/9999') as when creating the interval match table it reached 700m records before crashing!  I reduced the high end date to todays date, but that still was too much.

                     

                    I did manage to get it to work, but only on a much smaller product area, but that itself created a fairly large interval match table.

                     

                    Ideally it would be nice to see In-Force counts by day, but I think I will look to adjust your code and try and create intervals based on month-year, as this is the standard approach in our company for Policy In-Force reporting. 

                     

                    Michael.

                • Re: In-Force Policy Reporting
                  Artur Kaluza

                  Hi,

                   

                  I had exactly same situation to deal with. I used Intervalmatch but then i could only report In-Force policies by interval and I also needed to show New Policies and Cancelled/Expired policies.

                  The solution i appplies was not to use IntervalMatch but to calculate In-Force policies as the difference between accumulated volumes New Policies and accumulated volumes of Expired/ cancelled ones. You can obtain this for example by using Above() function in apivot table.

                  Please see my example explained:

                  //loading raw data

                  Data:

                  LOAD * INLINE [

                  Policy Number, StartDate, EndDate

                      P1, 01/01/2010, 30/06/2010

                      P2, 01/01/2010, 31/12/2010

                      P3, 01/01/2010, 31/12/2010

                      P4, 01/02/2010, 31/12/2010

                      P5, 01/06/2010, 30/06/2010

                      P6, 01/06/2010, 30/06/2010

                      P7, 01/06/2010, 31/07/2010

                      P8, 01/06/2010, 31/12/2010

                      P9, 01/06/2010, 31/12/2010

                      P10, 01/06/2010, 31/12/2010];

                  //bringing in 'Start' and 'End' flags for policy start dates and end date - all dates in one column

                  PoliciesFinal:

                  load

                  Policy,

                  StartDate as CalendarDate,
                  'Start'
                  as DateType //flag

                  resident Data;

                  //now add Enddates

                  load Policy,

                  Enddate as CalendarDate,
                  'End'
                  as DateType //flag

                  resident Data;

                  //MASTER CALENDAR:
                  //1. create mapping for Quarters

                  QuartersMap: 

                  MAPPING LOAD    rowno() as Month
                  'Q' &
                  Ceil (rowno()/3) as Quarter  AUTOGENERATE (12);

                  //2. define min & max date

                  LET vDateMin = Num(MakeDate(2010,1,1)); 

                  LET vDateMax = Num(MakeDate(2011,1,1));

                  //3. fill the gaps between min & max date

                  TempCalendar:

                  LOAD $(vDateMin) + RowNo() - 1 AS DateNumber

                  Date($(vDateMin) + RowNo() - 1) AS TempDate 

                  AUTOGENERATEWHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                  //4. create Master Calendar - take all dates created in the above step and calculate MonthNames, WeekNumbers,Quarters etc...

                  Master_Calendar: 

                  LOAD

                  Date(TempDate) AS CalendarDate

                  Month(TempDate) AS CalendarMonthName

                  Year(TempDate)&' Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

                  applymap('QuartersMap', Ceil(Month(TempDate)/3)) as MappedQ,

                  MonthName(TempDate) as CalendarMonthAndYear

                  MonthStart(TempDate) as CalendarMonthStart

                  MonthEnd(TempDate) as CalendarMonthEnd 

                  RESIDENT TempCalendar

                  ORDER BY TempDate ASC

                  DROP TABLE TempCalendar;

                   

                  Then I created a pivot table with the following:

                  Dimensions: CalendatMonthAndYear

                  Expressions:

                  Started =count({<DateType={'Start'}>} distinct Policy)

                  Expired/Cancelled =count({<DateType={'End'}>} Policy)

                  Live =rangesum(Above(Live),Started)

                  Dropped =rangesum(Above(Dropped),[Expired/Cancelled])

                  InForce =Live-Dropped

                   

                  Please see the attached file.

                  It works ok if you are happy with the initial assumption and the fact that you need to disallow pivoting because if you change the layout of the table the expressions will go wrong. Also, it shows the volume of in-force policies at the end of the month. IntervalMatch was showing the volumes at the beginning of the month but it was creating synthetic key resulting in loops and making New Policies and Expired Policies volumes incorrect.

                  There is a great post by HIC on IntervalMatch that explains how to overcome the synthetic key in needed but i haven't tried this yet: http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch 

                   

                  Best Regards,

                  Artur