3 Replies Latest reply on Sep 12, 2012 4:32 PM by Joe Mako

    Sum by Quarter End Date

    Scott Cabral

      Hi,

       

      We have homeowner's insurance data that is stored in a table on the last day of each month.

       

      So, for example, if we had 100 policies at the end of January 2012, then there would be 100 policy records in the table with a Report_Date of January 31st 2012.  If at the end of Febuary 2012 we had 120 policies, then we would append those records to the table with a Report_Date of Febuary 29, 2012.  March would have 150 policies, so 150 records with March 31st, 2012 and so on for the year.

       

      Each records includes policy level data like state, Line of Business, Premium Amt, Coverage Type, etc...

       

      I would like to create a worksheet that displays totals by Quarter, Month, Year (by possibly using a parameter).

       

      So if all of my policies had a Premium of $100, then the report would show:

       

           January     February      March

           $10,000     $12,000       $15,000

       

      Or

           Q1          Q2         Q3

           $37,000   

       

      Is there a way to do this aggregation by Quarter, Month, Year and display it on a worksheet?

       

      thanks

      Scott

        • 1. Re: Sum by Quarter End Date
          Tracy Rodgers

          Hi Scott,

           

          One way to do this is as you suggested creating a parameter and calculated field. The parameter would be a string parameter that lists out the different periods. Then, a calculated field would be created from it so it would look similar to the following:

           

          case [Choose your date period]

          when 'Year' then datename('year', [Order Date])

          when 'Quarter' then 'Q' + datename('quarter', [Order Date])

          when 'Month' then datename('month', [Order Date])

          end

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Sum by Quarter End Date
            Scott Cabral

            thank Tracy,

             

            Tha seems like it will help with breaking up the time periods, but I'm still struggling with the grouping of the measures.

             

            For example, let's say I wanted to look at the Premium for each year, quarter and month.  Here is a simplified snapshot of the data how it exists in the table for 2011:

             

            * We would be adding policies each month, so total monthly premium would increase each month.  I used $1000 increments for simplicicty

             

            01/31/2011 - Premium Sum of all policy records = $1000

            02/29/2011 - Premium Sum of all policy records = $2000

            03/31/2011 - Premium Sum of all policy records = $3000

            04/30/2011 - Premium Sum of all policy records = $4000

            05/31/2011 - Premium Sum of all policy records = $5000

            06/30/2011 - Premium Sum of all policy records = $6000

            07/31/2011 - Premium Sum of all policy records = $7000

            08/31/2011 - Premium Sum of all policy records = $8000

            09/30/2011 - Premium Sum of all policy records = $9000

            10/31/2011 - Premium Sum of all policy records = $10000

            11/30/2011 - Premium Sum of all policy records = $11000

            12/31/2012 - Premium Sum of all policy records = $12000

             

            So, I need to display that the Yearly Premium for 2011 would be $12,000 (Premium at end of 12/31/2012)

             

            I also need to display Quarterly Premium as Q1 = $3000 (Premium at 03/31/2011), Q2 = $6000 (Premium at 6/30/2011), Q3 = $8000 (Premium at 8/31/2011), and Q4 = $12,000 (Premium at 12/31/2011).

             

            And lastly display the monthly amounts as they exist above. (Jan = $1000, Feb = $2000, March = $3000, etc...).

             

            Is there a way to sum each month's premium to get the numbers above, but then display them in Tableau at either the Yearly, Quarterly, or Monthly level?

             

            Scott