0

Background In Insurance, a loss development triangle breaks down the cumulative costs (IE: Dollars paid for claims) over consistent development periods. The purpose is to help you see the progression of claim costs year by year over the same period of cost development.

For example, if I have claims that happened in 2008 & claims that happened in 2009, a triangle would help see the cumulative cost for both sets of claims after 12 months, then 24 months, etc. side by side.

The triangle would look something like this.

http://www.reserveprism.com/images/reportCountTriangle.jpg

The Question My question is, does an MDX query exist to create this or is it even possible within the framework of an OLAP cube? (I haven't been able to find anything on google, which is surprising because this is a standard insurance function).

user1118321
  • 25,567
  • 4
  • 55
  • 86
user3545525
  • 1
  • 1

1 Answers1

0

Not sure of your cube's structure but giving it a shot nonetheless.

with set [12 Months back] as --Gets you the year 12 months(1 year) from now
parallelperiod(
[Date].[Year].[Year]
,-1
,[Date].[Year].currentmember)

member [Measures].[12 Months] as --Calculates the "Dollars paid for claims" for the year calculated above.
AGGREGATE([12 Months back], [Measures].[Dollars paid for claims])

member [12] as --Gets the difference
[Measures].[12 Months] - [Measures].[Dollars paid for claims]

 set [24 Months back] as
parallelperiod(
[Date].[Year].[Year]
,-2
,[Date].[Year].currentmember)

member [Measures].[24 Months] as
AGGREGATE([24 Months back], [Measures].[Dollars paid for claims])

member [24] as
[Measures].[24 Months] - [Measures].[Dollars paid for claims]

 set [36 Months back] as
parallelperiod(
[Date].[Year].[Year]
,-3
,[Date].[Year].currentmember)

member [Measures].[36 Months] as
AGGREGATE([36 Months back], [Measures].[Dollars paid for claims])

member [36] as
[Measures].[36 Months] - [Measures].[Dollars paid for claims]

 set [48 Months back] as
parallelperiod(
[Date].[Year].[Year]
,-4
,[Date].[Year].currentmember)

member [Measures].[48 Months] as
AGGREGATE([48 Months back], [Measures].[Dollars paid for claims])

member [48] as
[Measures].[48 Months] - [Measures].[Dollars paid for claims]

 set [60 Months back] as
parallelperiod(
[Date].[Year].[Year]
,-5
,[Date].[Year].currentmember)

member [Measures].[60 Months] as
AGGREGATE([60 Months back], [Measures].[Dollars paid for claims])

member [60] as
[Measures].[60 Months] - [Measures].[Dollars paid for claims]

select {[Measures].[12], [Measures].[24], [Measures].[36], [Measures].[48], [Measures].[60]}  on 0,
[Date].[Year].members on 1
from [Your Cube]
SouravA
  • 5,147
  • 2
  • 24
  • 49