5 Replies Latest reply on Feb 22, 2018 8:28 AM by sana.masroor

# Claims Triangle - Running Sum that stops running at the current period.

I'm trying to create a claims triangle in Tableau.

This is a cross tab that has loss dates in the rows and loss development periods in the columns. These development periods put transactions into buckets based on the amount of time that has elapsed since the loss date. For example 0-29 days post loss, 30-59days 60-89 days etc.

I want the table to do a running total, but I want that running total to stop when there is no possibility of transactions. For example, for September 2012 loss dates, I should not have anything in the cell corresponding to 30-59 days post loss, because that is in the future!

The table is called a triangle because it should look like a triangle - no data in cells that are in the future, data in cells that are in the past.

• ###### 1. Re: Claims Triangle - Running Sum that stops running at the current period.

I am trying to accomplish the same thing and have done so, but in reverse using the book date on the X axis...but unlike the buckets the correct values are not in their appropriate place for real comparison. I am actively working on this now, and if you have found a way to solve for it please let me know!

• ###### 2. Re: Claims Triangle - Running Sum that stops running at the current period.

Miles,

Is the attached what you need? Here is how I put it together:

1) Create calculated field with the same formula you are using to calculate the running sum:

Running Sum of [Incurred Indemnity and Expenses] = RUNNING_SUM(SUM([Incurred Indemnity and Expenses]))

2) Create another calculated field that will display the above only if there are records in the data for that particular month/bin combination:

[Display of Running Sum] = IF WINDOW_MAX(MAX([Number of Records])) > 0 THEN [Running Sum of [Incurred Indemnity and Expenses]]] END

This is what you need to show on your table.

The trick is to use the edit table calculation dialog and set the [Display of Running Sum] to compute using cell while computing [Incurred Indemnity and Expenses] using [Transaction days after loss (bin)]

You do this by choosing each field from the calculated field drop down in the edit table calculation dialog. Keep in mind this relies on the presence of data for all valid bin/month combinations. If it's possible to have missing data in some months for which the combination is valid, you would have to modify this solution (or complete the data so it works).

Let me know if this works.

Pedro

• ###### 3. Re: Claims Triangle - Running Sum that stops running at the current period.

You are seriously a genius! Thank you for the help!

• ###### 4. Re: Claims Triangle - Running Sum that stops running at the current period.

I have a similar issue as this and I have been successful except for where have missing month / development days combinations.  If there is no activity on any claims from 90-120 days for a specific month, there is no data for that row column combination and the cell shows as blank.  For the next period, if there is activity the table calculation continues to work.

Is there a way to fill in these blanks when there are no records?

• ###### 5. Re: Claims Triangle - Running Sum that stops running at the current period.

This post was super helpful in creating loss triangles. My requirement is to take this calculation further and calculate weighted averages. What I am trying to achieve is dividing sum of A by sum of B as shown in

attached image. Please notice that we are not considering the last value of the triangle from the first time bin in calculation. Any help is much appreciated. 