The difference between a measure on two dimensions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

The difference between a measure on two dimensions

Author

Topic

TheOtherTruth
Starting Member

3 Posts

Posted - 2011-05-24 : 09:18:25

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,

Josh

TheOtherTruth
Starting Member

3 Posts

Posted - 2011-05-25 : 09:56:56

OK, i know no one has replied yet, but i thought i'd put a bit more detail in...

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!!

TheOtherTruth
Starting Member

3 Posts

Posted - 2011-05-25 : 10:13:20

Hi guys pavel pawlowski answered this for me on SQL Serveer Central,

here is the link to the thread

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

and here is the answer:

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]

Thanks Anyway!

TheOtherTruth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources