In-Force Policy Reporting

dldeboer — Mon, 18 Mar 2013 22:40:34 GMT

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

topic In-Force Policy Reporting in QlikView

In-Force Policy Reporting