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.
// 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: MAPPINGLOAD rowno() as[InForce Month], 'Q' & Ceil (rowno()/3) as[InForce Quarter] AUTOGENERATE (12);