7 Replies Latest reply on Jan 20, 2014 11:40 AM by kettan

# Chain-ladder table calculation - Question 3

Question:  How to make a Grand Total for IBNR/IBNER and Dev First Year as illustrated in screenshot below?

I assume it has to be a calculation of its own in a separate worksheet. This is acceptable.

See more in attached workbook and following document and threads:

Chain-ladder IBNR - step by step guide

Chain-ladder table calculation - Question 2

Chain-ladder table calculation - Question 1

.

• ###### 1. Re: Chain-ladder table calculation - Question 3

What computation should the Grand Total use? Sum of the displayed marks?

• ###### 2. Re: Chain-ladder table calculation - Question 3

Yes, Sum of the displayed marks. Thanks for taking a look at this.

• ###### 3. Re: Chain-ladder table calculation - Question 3

And the worksheet with the columns [IBNR/IBNER] and [Dev First Year] can also be solved in another way. The important thing is that the solution returns the same (correct) result as shown in the screenshot.

For learning purposes it would be curious to see if [IBNR/IBNER] and [Dev First Year] and Grand Totals could be included in the same worksheet as the main Chain-ladder worksheet. That said, I think it is better to separate these extra columns (as originally suggested), because it makes it easier (less difficult) to control its design, format, and grand totals.

• ###### 4. Re: Chain-ladder table calculation - Question 3

I'd just take the lazy route here and add another layer of table calcs .

All I did was window_sum() the two fields on duplicate of the sheet. I then add another copy of the Last filter with this one running table down so as to keep only one row.

Then I combined this on the dashboard in a vertical layout container to show the totals with the underlying numbers.

I hope this helps.

Dan

1 of 1 people found this helpful
• ###### 5. Re: Chain-ladder table calculation - Question 3

Thanks again, Dan!

Ps.  Chain-ladder IBNR - step by step guide  is updated accordingly.

Ps. This works in production too and includes now all calculations needed. The next step is to present it to potential users, so they know where to find it when needed.

• ###### 6. Re: Chain-ladder table calculation - Question 3

I really love this solution, and anything else related to Tableau and Insurance (particularly casualty).

Would it be possible to add a parameter that would allow you to attenuate large losses at the report level and not the database level?

e.g. calculate IBNR, but then use a parameter (or something similar) to smooth out any claims above a variety of values (i.e. \$100k, \$250k, \$500k...etc)?

• ###### 7. Re: Chain-ladder table calculation - Question 3

I wonder if the set filter technique shared by Joe in  Count claims with reserve (exclude claims with no reserve)  would do what you think of? The genius of it is that it filters out detail rows based on an aggregated calculation!