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.
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,
"GOVERNING_CLASS_CODE_ID" AS CLASS_CODE_ID;
// 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.
rowno() as [InForce Month],
'Q' & Ceil (rowno()/3) as [InForce Quarter]
min([Effective Date]) as minDate,
max([Expiration Date])as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
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]
Order By TempDate ASC;
Drop Table TempCalendar;
// Connect the InForceCalendar and the Policy tables using the IntervalMatch function
[Expiration Month for IntervalMatch]
Message was edited by: dldeboer