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

MDX First month of year Expand / Collapse
Author
Message
Posted Sunday, March 10, 2013 1:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 1,166, Visits: 1,191
This shouldn't be as hard as it seems; I want to always get the first month of the year (January) regardless of what month is the current member. I'm going to use this later on (if I get it to work) for a calculated member. The Date dimension's calendar hierarchy is Year --> Quarter --> Month.

So I've tried:

-- Returns back the year okay --
SELECT {} ON ROWS,
Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Year]) ON COLUMNS
FROM [RoyaltyPMA]
WHERE [Date].[Month].&[201209];


But shouldn't this return back the first quarter of the year?:
-- Returns back nothing --
SELECT {} ON ROWS,
Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Year]).FirstChild ON COLUMNS
FROM [RoyaltyPMA]
WHERE [Date].[Month].&[201209];


Or, couldn't I get the .FirstSibling of the .CurrentMember (if CurrentMember is at the month level) like. I've also unsuccessfully tried using the OpeningPeriod.

It must be too late at night; any pointing me in the correct direction would be appreciated.

Thanks,
Rob
Post #1428963
Posted Sunday, March 10, 2013 10:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 1,166, Visits: 1,191
I'm sure there must be a better way, but I did get this to work by:

MEMBER [Measures].[Yearly Adv Req] AS
(
Ancestor( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Year] ).FirstChild.FirstChild,
[Measures].[Advertising Req]
)


Rob
Post #1428994
Posted Thursday, March 14, 2013 11:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
It's probably how I would have done it

Alternatively you could do

MEMBER [Measures].[Yearly Adv Req] AS   
(Descendants(Ancestor( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Year] ),2).Item(0)
, [Measures].[Advertising Req] )


Mack
Post #1431141
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse