none
Difference between a measure on two dimensions

    Question

  • Hi,

    I'm pretty new to MDX and I have been bashing my head against this for a while...

    What i need to do is display the difference between the value of a measure on two different dimensions, an example in adventure works might be that i want the difference between the number of orders placed and the number of orders delivered on a date.

    So to do this i need to get the value of [Order Count] along the [Delivery Date].[Calendar] hierarchy and take that from the value of [Order Count] along the [Date].[Calendar] hierarchy at the correct level, which seems to be more complicated than first expected!

    I've tried using LinkMember, it doesn't seem to get me what i need any one out there have any ideas?

     

    Thanks,

    TheOtherTruth


    Tuesday, May 24, 2011 1:12 PM

Answers

  • Hi,

     

    just after i posted the last lot i saw on sql server central that Pavel Pawlowski had answered with a neat solution, the full thread can be found here...

     

    http://ask.sqlservercentral.com/questions/72968/difference-between-a-measure-on-two-date-dimensions

     

    thanks for the help anyway!

     

    and his solution is this:

     

    WITH
      MEMBER [Measures].[Delivered] AS (
        [Date].[Calendar Year].[All Periods],
        LinkMember([Date].[Calendar].CURRENTMEMBER, 
        [Delivery Date].[Calendar]), [Measures].[Order Quantity]
      ), SOLVE_ORDER = 1
      MEMBER [Measures].[Diff] AS ([Measures].[Order Quantity] - [Measures].[Delivered]), SOLVE_ORDER = 2
    SELECT 
      {([Measures].[Order Quantity]), ([Measures].[Delivered]), ([Measures].[Diff])} ON COLUMNS,
      [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS
    FROM [Adventure Works]
    • Marked as answer by TheOtherTruth Wednesday, May 25, 2011 2:06 PM
    Wednesday, May 25, 2011 2:06 PM

All replies

  • LinkMember should work. What code were you using and what was the issue you were seeing?
    http://darren.gosbell.com - please mark correct answers
    Wednesday, May 25, 2011 7:29 AM
  • Hi Darren, thanks for the interest,

    I have gotten a bit further with this today, but still not solved it.

     

    the code i am using at the minute looks a bit like this:

     

    WITH

    MEMBER [Measures].[Orders Delivered]
    AS
        SUM
        (
            (
                EXCEPT
                (
                [Date].[Calendar Quarter Of Year].CHILDREN
                ,[Date].[Calendar Quarter Of Year].UNKNOWNMEMBER
                )
            )
                *
            (
                LINKMEMBER([Date].[Calendar Quarter Of Year].CURRENTMEMBER, [Delivery Date].[Calendar])
                ,[Measures].[Order Quantity]
            )
        )
       
    SET SelectedCrQuarters as
    LASTPERIODS(8,[Date].[Calendar].[Calendar Quarter].&[2004]&[2])

    MEMBER [Measures].[Balance]
    AS
    (
        [Measures].[Order Quantity] - [Measures].[Orders Delivered]
    )

    SELECT
     {[Measures].[Order Quantity], [Measures].[Orders Delivered],[Measures].[Balance]} ON COLUMNS,
     SelectedCrQuarters ON ROWS
    FROM [Adventure Works]

     

    And the problem with this is that i am not getting back any value in the Orders Delivered column, so the Balance is coming out wrong.

     

    Previously i tried

    MEMBER [Measures].[Orders Delivered]
    AS (LINKMEMBER([Date].[Calendar].CURRENTMEMBER, [Delivery Date].[Calendar]),[Measures].[Order Quantity])  

     

    but that only gave me the values at the intersect of the two, i.e. the count when [Date] = [Delivery date] whihc isn't really what i want.

     

    Any ideas would be gratefully received!!

    Wednesday, May 25, 2011 1:55 PM
  • Hi,

     

    just after i posted the last lot i saw on sql server central that Pavel Pawlowski had answered with a neat solution, the full thread can be found here...

     

    http://ask.sqlservercentral.com/questions/72968/difference-between-a-measure-on-two-date-dimensions

     

    thanks for the help anyway!

     

    and his solution is this:

     

    WITH
      MEMBER [Measures].[Delivered] AS (
        [Date].[Calendar Year].[All Periods],
        LinkMember([Date].[Calendar].CURRENTMEMBER, 
        [Delivery Date].[Calendar]), [Measures].[Order Quantity]
      ), SOLVE_ORDER = 1
      MEMBER [Measures].[Diff] AS ([Measures].[Order Quantity] - [Measures].[Delivered]), SOLVE_ORDER = 2
    SELECT 
      {([Measures].[Order Quantity]), ([Measures].[Delivered]), ([Measures].[Diff])} ON COLUMNS,
      [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS
    FROM [Adventure Works]
    • Marked as answer by TheOtherTruth Wednesday, May 25, 2011 2:06 PM
    Wednesday, May 25, 2011 2:06 PM