locked
Comparing two time dimensions with LinkMember RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

     

    I have to make a measure which gives me an information about costs posted inside specific period on a specific document. I get it with LinkedMember. I get it with comparing two time dimensions, which have same structure and same source table, just different names and they use different key column in a fact table. Everything works fine until I specify the mdx statement for all hierarchies in date dimension.

    Date Dimension: Date and Adjustment Date

    Date Hierarchies: I have 6 different hierarchies, 3 for calendar and 3 for fiscal calendar. Key is DateID and most granular attribute is Day. 

    Measure I have done and it's working and gives me correct results:

     

    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Inside]

     AS (LINKMEMBER([Date].[Date YMD].currentmember, [Adjustment Date].[Date YMD]), [Measures].[Cost]), 

    FORMAT_STRING = "#,#.00", 

    VISIBLE = 1  ; 

    ([Measures].[Cost Inside], [Date].[Date YQMD]) = (LINKMEMBER([Date].[Date YQMD].currentmember, [Adjustment Date].[Date YQMD]), [Measures].[Cost]);

    ([Measures].[Cost Inside], [Date].[Date YWD]) = (LINKMEMBER([Date].[Date YWD].currentmember, [Adjustment Date].[Date YWD]), [Measures].[Cost]);

    But when I add next rows, nothing is working I don't get any result just empty rows.
    ([Measures].[Cost Inside], [Date].[F Date YMD]) = (LINKMEMBER([Date].[F Date YMD].currentmember, [Adjustment Date].[F Date YMD]), [Measures].[Cost]);
    ([Measures].[Cost Inside], [Date].[F Date YQMD]) = (LINKMEMBER([Date].[F Date YQMD].currentmember, [Adjustment Date].[F Date YQMD]), [Measures].[Cost]);
    ([Measures].[Cost Inside], [Date].[F Date YWD]) = (LINKMEMBER([Date].[F Date YWD].currentmember, [Adjustment Date].[F Date YWD]), [Measures].[Cost]);
    I hope I have explained enough good. Can somebody help me, what I did in wrong way?
    Thanks in advance.

     

     

    Friday, November 11, 2011 9:59 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    go ahead with following script:

    --To merge the above 2 calculated memebrs into one

    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Inside]

     AS Null,

    FORMAT_STRING = "#,#.00",

    VISIBLE = 1  ;

    --Scope1: if the [Measures].[Cost Inside] is used in calendar dates, then it references [Measures].[Cost Inside in Calendar Dates]

    Scope([Measures].[Cost Inside],{Dates related to calendar});
    This = [Measures].[Cost Inside in Calendar Dates];
    End Scope;

    --Scope2;invoke [Measures].[Cost Inside in Fiscal Dates] when [Measures].[Cost Inside] is used in Fiscal dates.

    Scope([Measures].[Cost Inside],{Dates related to Fiscal});
    This = [Measures].[Cost Inside in Fiscal Dates];
    End Scope;

    Hope this helpfully,

    regards,
    Jerry

    • Marked as answer by Zanka Thursday, November 17, 2011 7:33 AM
    Monday, November 14, 2011 6:23 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Zanka,

    Apparently the Fiscal hierarchies did not exists in the calculated member definition, so, the left expression(e.g. ([Measures].[Cost Inside], [Date].[F Date YQMD])) can't be resolved.

    To resolve it, i assumed that you had defined at least 2 user-hierarchies for calendar and fiscal. So, then you can achieve your purpose by creating 2 calculated members separately for calendar and fiscal members. For example,

    -- For calendar date members

    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Inside in Calendar Dates]

     AS (LINKMEMBER([Date].[CalendarHierarchy].currentmember, [Adjustment Date].[CalendarHierarchy), [Measures].[Cost]),

    FORMAT_STRING = "#,#.00",

    VISIBLE = 1  ;

    --For Fiscal date members

    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Inside in Fiscal Dates]

     AS (LINKMEMBER([Date].[FiscalHierarchy].currentmember, [Adjustment Date].[FiscalHierarchy]), [Measures].[Cost]),

    FORMAT_STRING = "#,#.00",

    VISIBLE = 1  ;

    thanks,
    Jerry

    Monday, November 14, 2011 2:57 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jerry,

     

    I know, that if I make two different calculations, one for fiscal and one for calendar hierarchy, it works. But, I would like to do it for both hierarchies in one measure, because I have both hierarchies in one dimension. Strange is, when I define for fiscal calendar, together with "normal" calendar, nothing is working, even "normal" calendar stops to work, and as a result gives me 0, where there should be something. 

     

    Thanks,

    Zanka

    Monday, November 14, 2011 6:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    go ahead with following script:

    --To merge the above 2 calculated memebrs into one

    CREATE MEMBER CURRENTCUBE.[Measures].[Cost Inside]

     AS Null,

    FORMAT_STRING = "#,#.00",

    VISIBLE = 1  ;

    --Scope1: if the [Measures].[Cost Inside] is used in calendar dates, then it references [Measures].[Cost Inside in Calendar Dates]

    Scope([Measures].[Cost Inside],{Dates related to calendar});
    This = [Measures].[Cost Inside in Calendar Dates];
    End Scope;

    --Scope2;invoke [Measures].[Cost Inside in Fiscal Dates] when [Measures].[Cost Inside] is used in Fiscal dates.

    Scope([Measures].[Cost Inside],{Dates related to Fiscal});
    This = [Measures].[Cost Inside in Fiscal Dates];
    End Scope;

    Hope this helpfully,

    regards,
    Jerry

    • Marked as answer by Zanka Thursday, November 17, 2011 7:33 AM
    Monday, November 14, 2011 6:23 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks Jerry. 
    Thursday, November 17, 2011 7:33 AM