Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculating MTD, YTD and ITD Expand / Collapse
Author
Message
Posted Tuesday, November 5, 2013 8:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 8:35 AM
Points: 20, Visits: 151
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;
Post #1511510
Posted Wednesday, November 6, 2013 3:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:40 AM
Points: 451, Visits: 847
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
Post #1511769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse