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

# Sum by Quarter End Date

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

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:

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

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