9 Replies Latest reply on May 9, 2012 6:29 AM by Peter Thomsen

    calculate number of insurance policies over time

    Anne Omland

      An insurance can in any given time period be either
      a. Active
      b. Not active

       

      If one looks at a given time period, active insurances can be categorized as either unchanged, new, short-time or drop-out.

       

      1. Unchanged: The insurance was active at the start and at the end of the period.
      2. New: The insurance was not active at the start of the period but it was active at the end of the period.
      3. Short time: The insurance was neither active at the start nor at the end of the period, but it was active some time during the period.
      4. Drop-out: The insurance was active at the start of the period but not at the end of the period.

       

      The aim is to, given a data set only containing effective and expiration dates for insurances, plot the percentages of the four categories mentioned above for each time period. This is easily done for one time period. However, I have not been able to calculate the number of policies that fall into each of the categories over time as I do not have a date variable that fits as neither effective date nor expiration date would be correct to use as the time axis.

       

      One might for instance be interested in looking at how the monthly drop-out rate has changed over the last three years, or a diagram showing the percentage of the insurance policies that were new in each and every week over the last five years.

       

      In order to do this we also want to be able to choose:
      - Date level for the period we want to base the calculations on (days, weeks, months, years)
      - How many such periods back in time we want the graph for, e.g. 13 weeks back.

       

      Attached are both a Tableau workbook with mock data and an excel file where I have tried to show what I would like to do (using manual calculations).

       

      Regards ,

      Anne

        • 1. Re: calculate number of insurance policies over time
          Alex Kerin

          Tricky one. This is a queue problem - part of the solution will probably be custom sql where we increment and decrement the number of policies - something like this:

           

           

          SELECT 1 as [Delta],

            [Sheet1$].[Effective date] AS [Date]

          FROM [Sheet1$]

          UNION ALL

          SELECT -1 as [Delta],

            [Sheet1$].[Expiration date] AS [Date]

          FROM [Sheet1$]

           

          But I think we may also need to pad the dates out as well/instead (adding a list of the days/weeks). I think Jonathan Drummey did something similar a while back. I'm not sure the date of 5999 is helpful - it may be best to leave this as blank (i.e. no expiration date)

           

          Sorry this doesn't get you much closer.

          • 2. Re: calculate number of insurance policies over time
            Anne Omland

            Thank you for your response Alex.

            Do you have any idea how to solve this Jonathan Drummey?

             

            regards,

            Anne

            • 3. Re: calculate number of insurance policies over time
              Jonathan Drummey

              Hi Anne,

               

              I'd noted this post as something to look at, and I agree with Alex that it's a queuing problem that requires padding the data, the reason being that the "background" you want to calculate over is the time period. Tableau doesn't like to draw data where there isn't any, so you need to pad the data set.

               

              I'm thinking that if you start with a list of days (generated by Custom SQL or by having a table of days), then you can have that as the "original" data that you would then join (using a left join) something like the queuing calculation that Alex wrote. That way you'd have every day available for a discrete (blue pill) date dimension, and would be able to have a date series that could be used for the calcs for plotting how many policies were added/removed within that period. One thing to be careful of is you will always have a record for every day, so inside your calculations you'll need to filter out counting the records that are just there for padding.

               

              If you need more help than that, let me know and I'll be able to give you a more concrete solution in the next day or two.

               

              Jonathan

              • 4. Re: calculate number of insurance policies over time
                Jonathan Drummey

                I dug up the work that Alex referenced, here's the post (that references other posts):

                 

                http://community.tableau.com/thread/116755?start=15&tstart=0

                • 5. Re: calculate number of insurance policies over time
                  Jonathan Drummey

                  Hi Anne,

                   

                  A question for you - in each time period e.g. week-1, week-2, week-3, etc. it seems like you want to show the totals as they exist for that time period. Is that correct? I think I might be confusing myself about what the desire result is.

                   

                  Jonathan

                  • 6. Re: calculate number of insurance policies over time
                    Anne Omland

                    Hi Jonathan,

                    For each time period we want the calculated value to be the percentage of the different categories for that time period, that is: If we are looking at week 4, we want to calculate the percentages of the four different categories and accredit them to the week we are doing the calculations for (as in the excel graph).

                    So, I don't think that we need to show the total for each time period per se, rather we need to figure out a smart way of filtering the time period since there are two dates (effective and expiration) for each observation.

                    Note that doing this calculation for the whole dataset is extremely easy, the problem is how to do it dynamically.

                     

                    -Anne

                    • 7. Re: calculate number of insurance policies over time
                      Peter Thomsen

                      Hi Anne

                      I may have a different way to look at it.

                       

                      Could you convert the data source to give a delta number on each change of status?

                      I have tried to make an example where I use running sum to show the number in each status on each day

                       

                      //Peter

                      1 of 1 people found this helpful
                      • 8. Re: calculate number of insurance policies over time
                        Anne Omland

                        Hi Peter,

                         

                        Really interesting approach, and I am sure this might work if we tweek it a bit. For instance, now the drop out numbers increase constanty over time, a reason for this might be that we might need to a add a "trash bin"/"expired" status, what do you think?

                         

                        Furthermore, from the same data set we need to be able to calculate the "Short time" (The insurance was neither active at the start nor at the end of the period, but it was active some time during the period) category, how this is done I am not certain of.

                         

                        Thank you heaps for your help,

                         

                        Best,

                         

                        -Anne

                        • 9. Re: calculate number of insurance policies over time
                          Peter Thomsen

                          Hi Anne

                           

                           

                          You decide how long time you want a policy marked with a dropout by creating a new line in your  data set with dropout -1

                           

                          The short policies that runs for less then the period chosen can be a problem. One way it could be handled is by making a calculated field like this:

                               if [Status] = "Short" and [NumberOfPolicies] = 1 then 1 else 0 end

                          where you count the number of Short policies.

                          Then use it in a dual axis chart where you change the marktype of the "Short" measure to bar or something else.

                           

                          Hope this helps

                           

                          //Peter