## Calculating MTD, YTD and ITD

 Author Message f9c882q 61644 SSC Veteran Group: General Forum Members Points: 202 Visits: 170 I have developed a Cube for client and want to implement the time intelligence on Date, Month and Year hierarchy but can't get it to work. I used the built in Time intelligence to create the script below unfortunately the same dollar values are repeated for ITD, MTD and YTD. Any help will be appreciated. Thanks in advanceCREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[ITD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[YTD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; CREATE MEMBER CURRENTCUBE.[ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[MTD] AS "NA", FORMAT_STRING = "Currency", VISIBLE = 1 ; Scope( { [Measures].[LossPaid], [Measures].[LossReserve], [Measures].[LossIncurred], [Measures].[LAEPaid], [Measures].[LAEReserve], [Measures].[LAEIncurred] }); // ITD ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[ITD], [ClaimsTransactionDate].[Year].[Year].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Year], [ClaimsTransactionDate].[Calender].CurrentMember ) ); // Year to Date ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[YTD], [ClaimsTransactionDate].[Year].[Year].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Year], [ClaimsTransactionDate].[Calender].CurrentMember ) ); // Month to Date ( [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[MTD], [ClaimsTransactionDate].[Month].[Month].Members, [ClaimsTransactionDate].[Transaction Date].Members ) = Aggregate( { [ClaimsTransactionDate].[Calender ClaimsTransactionDate Calculations].[Current ClaimsTransactionDate] } * PeriodsToDate( [ClaimsTransactionDate].[Calender].[Month], [ClaimsTransactionDate].[Calender].CurrentMember ) ); End Scope; PB_BI SSChampion Group: General Forum Members Points: 10157 Visits: 2983 Take a look at Marco Russo's brilliant DateTool dimension. It does a similar thing to the Time Intelligence stuff but it's much better and more configurable to whatever your needs are: http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx Im on LinkedIn