8 Replies Latest reply on Mar 27, 2012 1:31 PM by davekeller

    How to show time series of insurance policies in force

      How can I show time series of our insurance policies "In Force?" base data is by policy, each with an effective date and expiration date. Each policy is "in force" between the effective and expiration dates if its status remains "bound." Most policies are in force for a year. I want a graph of outstanding sum of "In Force" premium each month over the last several years. In other words, a typical policy would show up in 12 monthly periods from effective date before rolling off.

        • 1. Re: How to show time series of insurance policies in force
          Alex Kerin

          It would be really helpful if you could provide an example of the data (mocked up if sensitive) and a sketch of what you would like the chart to look like.

          • 2. Re: How to show time series of insurance policies in force

            Thanks, here's a sample with modified submission names.

            "InForce" is defined as an SQB_Stage of 5_Bound between the Effective_Date and Expiration_Date.

            The objective is to have a monthly time series graphic over several years showing the sum of InForce each month. So, nearly all policies will show up in twelve consecutive months.

             

            Once that is accomplished I can put other dimensions on the color shelve to break the whole thing down by type. But the first issue is getting the InForce Sum up for each month.

             

             

             

            NAME

             

            AMOUNT

             

            EFFECTIVE_DATE__C

             

            EXPIRATION_DATE__C

             

            REFERENCEID__C

             

            SQB_STAGE__C

             

            STAGENAME

             

            Submission name 001

             

            93619

             

            5/6/2012

             

            5/6/2013

             

            DarrellT12

             

            2_Declined

             

            Declined

             

            Submission name 002

             

            15000

             

            4/1/2012

             

            4/1/2013

             

            GrandRap12

             

            2_Declined

             

            Declined

             

            Submission name 003

             

            150000

             

            2/28/2012

             

            2/28/2013

             

            RemoteMD12 1

             

            2_Declined

             

            Declined

             

            Submission name 004

             

            153702

             

            4/6/2012

             

            4/6/2013

             

            Catalyst12 3

             

            2_Declined

             

            Declined

             

            Submission name 005

             

            150000

             

            3/31/2012

             

            3/31/2013

             

            Illinois12 6

             

            2_Declined

             

            Declined

             

            Submission name 006

             

            97392

             

            3/15/2012

             

            3/15/2013

             

            AlumiGua12 1

             

            2_Declined

             

            Declined

             

            Submission name 007

             

            192455

             

            5/1/2012

             

            5/1/2013

             

            MCNConst12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 008

             

            15000

             

            4/1/2012

             

            4/1/2013

             

            PanellaT12

             

            2_Declined

             

            Declined

             

            Submission name 009

             

            69945

             

            5/25/2012

             

            5/25/2013

             

            WilcoEnt12 1

             

            2_Declined

             

            Declined

             

            Submission name 010

             

            898386

             

            4/27/2012

             

            4/27/2013

             

            Californ12 13

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 011

             

            100215

             

            5/28/2012

             

            5/28/2013

             

            NassauMa12

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 012

             

            31970

             

            5/1/2012

             

            5/1/2013

             

            PureCoun12 1

             

            2_Declined

             

            Declined

             

            Submission name 013

             

            201869

             

            5/26/2012

             

            5/26/2013

             

            Dominion12 4

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 014

             

            149385

             

            4/1/2012

             

            4/1/2013

             

            AllPhase12 1

             

            2_Declined

             

            Declined

             

            Submission name 015

             

            52087

             

            4/1/2012

             

            4/1/2013

             

            Oglethor12 1

             

            2_Declined

             

            Declined

             

            Submission name 016

             

            0

             

            4/1/2012

             

            4/1/2013

             

            ShieldsM12

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 017

             

            13692

             

            3/26/2012

             

            3/26/2013

             

            JupiterP12 1

             

            3_Quoted

             

            Quoted

             

            Submission name 018

             

            0

             

            3/18/2012

             

            3/18/2013

             

            hollyBlu12

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 019

             

            229959

             

            3/1/2012

             

            3/1/2013

             

            Scorpion14 1

             

            2_Declined

             

            Declined

             

            Submission name 020

             

            57511

             

            4/2/2012

             

            4/2/2013

             

            DrakeCon12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 021

             

            136507

             

            4/1/2012

             

            4/1/2013

             

            LamettiS12

             

            2_Declined

             

            Declined

             

            Submission name 022

             

            30037

             

            4/28/2012

             

            4/28/2013

             

            Sensidyn12 1

             

            2_Declined

             

            Declined

             

            Submission name 023

             

            150000

             

            4/28/2012

             

            4/28/2013

             

            GrahamEn12

             

            2_Declined

             

            Declined

             

            Submission name 024

             

            156184

             

            5/27/2012

             

            5/27/2013

             

            SouthOak12

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 025

             

            62705

             

            4/15/2012

             

            4/15/2013

             

            JankeGen12 2

             

            2_Declined

             

            Declined

             

            Submission name 026

             

            0

             

            5/26/2012

             

            5/26/2013

             

            HarborFe12

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 027

             

            149912

             

            3/12/2012

             

            3/12/2013

             

            ColtonEl12 1

             

            2_Declined

             

            Declined

             

            Submission name 028

             

            14428

             

            4/4/2012

             

            4/4/2013

             

            PumaRaci12

             

            1_Pre-Quote

             

            Approval Requested

             

            Submission name 029

             

            3635

             

            4/1/2012

             

            4/1/2013

             

            ScopeLog12 1

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 030

             

            3635

             

            4/1/2012

             

            4/1/2013

             

            ScopeLog12

             

            2_Declined

             

            Declined

             

            Submission name 031

             

            86581

             

            4/1/2012

             

            4/1/2013

             

            Baseline12 2

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 032

             

            20747

             

            4/1/2012

             

            4/1/2013

             

            Baseline12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 033

             

            184229

             

            3/7/2012

             

            3/7/2013

             

            CSGarber12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 034

             

            165487

             

            4/1/2012

             

            4/1/2013

             

            CastAlum12

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 035

             

            26847

             

            5/3/2012

             

            5/3/2013

             

            GREATBAY12

             

            3_Quoted

             

            Quoted

             

            Submission name 036

             

            89004

             

            3/21/2012

             

            3/21/2013

             

            Townsend12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 037

             

            150000

             

            5/1/2012

             

            5/1/2013

             

            BurkeWil12 1

             

            2_Declined

             

            Declined

             

            Submission name 038

             

            150000

             

            6/1/2012

             

            6/1/2013

             

            Northwoo12 1

             

            2_Declined

             

            Declined

             

            Submission name 039

             

            250

             

            3/1/2012

             

            7/26/2012

             

            MuseumTo11 53

             

            5_Bound

             

            Issued

             

            Submission name 040

             

            14394

             

            5/12/2011

             

            5/12/2012

             

            TRIPLEHENGI0 11

             

            2_Declined

             

            Declined

             

            Submission name 041

             

            0

             

            5/25/2012

             

            5/25/2013

             

            RonnieSt12

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 042

             

            56256

             

            4/1/2012

             

            4/1/2013

             

            BakerWel12

             

            1_Pre-Quote

             

            Approval Requested

             

            Submission name 043

             

            88198

             

            3/31/2012

             

            3/31/2013

             

            Universa12 11

             

            2_Declined

             

            Declined

             

            Submission name 044

             

            338895

             

            4/4/2012

             

            4/4/2013

             

            ParkwayM12 1

             

            1_Pre-Quote

             

            Quote In Progress

             

            Submission name 045

             

            0

             

            5/26/2012

             

            5/26/2013

             

            PacificM12 4

             

            1_Pre-Quote

             

            New Cleared Application

             

            Submission name 046

             

            88847

             

            3/8/2012

             

            3/8/2013

             

            Benchmar12 3

             

            4_Lost

             

            Lost

             

            Submission name 047

             

            54812

             

            8/15/2011

             

            8/15/2012

             

            BOPCONTROLS2 11

             

            2_Declined

             

            Declined

             

            Submission name 048

             

            46618

             

            8/17/2011

             

            8/17/2012

             

            RAPIDENERGY0 11

             

            2_Declined

             

            Declined

             

            Submission name 049

             

            300000

             

            5/15/2011

             

            5/15/2012

             

            FOXRENTACAR0 11

             

            2_Declined

             

            Declined

             

            Submission name 050

             

            2772073

             

            4/1/2012

             

            4/1/2013

             

            MagInstr12 1

             

            1_Pre-Quote

             

            Quote In Progress

            • 3. Re: How to show time series of insurance policies in force
              Alex Kerin

              Could you attach that in an xlsx file if possible? It doesn't copy out of the browser well.

              • 4. Re: How to show time series of insurance policies in force

                Sure, Alex, thanks. Please see attahced. Again, the issue at hand is to take the records that have an SQB_Stage of 5_Bound and have their amount be added to the sum of all deals in each and every month between their effective date and expiration date.

                 

                Dave

                • 5. Re: How to show time series of insurance policies in force
                  Alex Kerin

                  Take a look at this as a start - we are using custom sql and a running sum. The sql breaks each row out into two, and adds one to the delta for each effective date, and one off for each expiration date. If you are going to use the data for other analyses, I would connect again to the source without the custom SQL. In this instance you are going to have double the number of records/amounts etc.

                  • 6. Re: How to show time series of insurance policies in force

                    Thank you, Alex! Really excellent solution! This makes a huge difference for us.

                     

                    I see that putting Delta On the Row Shelf graphed the number of policies during the period, and by simply dragging Amount there instead it gives me the InForce Premium.

                     

                    Much appreciated!

                     

                    Dave

                    • 7. Re: How to show time series of insurance policies in force
                      Alex Kerin

                      But beware - check the amount isn't doubled or otherwise wrong. When the policy expires, the amount is added again. you may want to change the second part of the custom SQL to decrement the amount as well as the delta - add the red minus symbol as below and then you can use a running sum on amount. You have to be very careful when the data is doubled.

                       

                      SELECT ['report1332863861964 1 $'].[Amount] AS [Amount],

                        ['report1332863861964 1 $'].[Effective Date] AS [Date],

                        1 as delta,

                        ['report1332863861964 1 $'].[Governing Class Code] AS [Governing Class Code],

                        ['report1332863861964 1 $'].[Opportunity Name] AS [Opportunity Name],

                        ['report1332863861964 1 $'].[Segments_Modified] AS [Segments_Modified],

                        ['report1332863861964 1 $'].[SQB Stage] AS [SQB Stage],

                        ['report1332863861964 1 $'].[Stage] AS [Stage]

                      FROM ['report1332863861964 1 $']

                      union all

                      SELECT -['report1332863861964 1 $'].[Amount] AS [Amount],

                        ['report1332863861964 1 $'].[Expiration Date] AS [Date],

                        -1 as delta,

                        ['report1332863861964 1 $'].[Governing Class Code] AS [Governing Class Code],

                        ['report1332863861964 1 $'].[Opportunity Name] AS [Opportunity Name],

                        ['report1332863861964 1 $'].[Segments_Modified] AS [Segments_Modified],

                        ['report1332863861964 1 $'].[SQB Stage] AS [SQB Stage],

                        ['report1332863861964 1 $'].[Stage] AS [Stage]

                      FROM ['report1332863861964 1 $']

                      • 8. Re: How to show time series of insurance policies in force

                        Thanks, Alex! That'll prevent much embarrassment. Thanks also for making it so clear where that minus sign is on amount!

                         

                        Dave