5 Replies Latest reply on Aug 16, 2013 4:22 AM by kettan

Chain-ladder table calculation - Question 1

OBS!  This question is continued in   Chain-ladder table calculation - Question 2

I want to make a chain-ladder table, and expect to ask for help in several rounds.

Question:

How do I change the formula in the attached workbook so it calculates only the blue area?

This is current table calculation as marked red in print screen below:

WINDOW_SUM(SUM([Amount]), FIRST(), 0) / WINDOW_SUM(SUM([Amount]), FIRST(), -1)

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

The formula below works, but has hard-coded values for the dimensions.

What I need is a dynamic formula without hard-coded conditions.

IF ATTR([Development Year]) = 1 THEN

WINDOW_SUM(SUM(IF [Incident Year]<=2011 THEN [Amount] END), FIRST(), 0) /

WINDOW_SUM(SUM(IF [Incident Year]<=2011 THEN [Amount] END), FIRST(), -1)

ELSEIF ATTR([Development Year]) = 2 THEN

WINDOW_SUM(SUM(IF [Incident Year]<=2010 THEN [Amount] END), FIRST(), 0) /

WINDOW_SUM(SUM(IF [Incident Year]<=2010 THEN [Amount] END), FIRST(), -1)

ELSEIF ATTR([Development Year]) = 3 THEN

WINDOW_SUM(SUM(IF [Incident Year]<=2009 THEN [Amount] END), FIRST(), 0) /

WINDOW_SUM(SUM(IF [Incident Year]<=2009 THEN [Amount] END), FIRST(), -1)

END

• 2. Re: Chain-ladder table calculation - Question 1

Johan I spent a bit of time looking at this and didn't get anywhere. I mention this because I didn't want you to think none of us tried.

Cheers,

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

Johan--

The attached has the answer correct. This is a very complicated calculation. I am happy to add captions explaining each of the calcs but for now I just wanted to give you your solution.

Look for an update later tonight or tomorrow.

Hope this helps,

Dan

2 of 2 people found this helpful
• 4. Re: Chain-ladder table calculation - Question 1

Thanks Dan,

These are precious formulas for learning table calculations and hopefully also the first step toward a workable chain-ladder IBNR for the task at hand.

I will prepare a question 2 which will build on your calculations and also share an Excel spreadsheet with working formulas for easy comparison.

Jóhan

• 5. Re: Chain-ladder table calculation - Question 1

Thanks Shawn, this was heart-warming.

1. Getting feedback without a workable solution feels much better than being ignored.
2. Knowing it probably can't be done or will be very time-consuming also makes it easier to switch to a task with high impact and low effort rather than spending more time on a task that needs high effort and possibly no impact.
3. It also makes me feel less stupid that I am not alone having difficulties with table calculations.
That said, I will be among the first in the line to buy the book "Tableau Table Calculations for Dummies" when it hopefully is released.

Jóhan