Calculating MTD, YTD and ITD

  • 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 advance

    CREATE 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;

  • 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


    I'm on LinkedIn

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply