I have a cube with a  single fact: [Measures].[order count] and two roleplaying dimensions [Order Date] and [Shipped Date]

I looking for calculated member that counts the order only if the Order Date and Shipped Date Occur in the same month.

I can do this as a TSQL case statement in the ETL; but I'm curious if there is a MDX based solution?

I'm using MSSAS 2008 R2

 

 

With Set Dates as
	Generate(
		NONEMPTY(
				[Date].[Calendar].[Date]*
				[Ship Date].[Calendar].[Date],
				[Measures].[Internet Order Count]
			),
			FILTER
		(
			([Date].[Calendar].currentmember,[Ship Date].[Calendar].currentmember),
			[Date].[Calendar].currentmember.membervalue
			=[Ship Date].[Calendar].currentmember.membervalue
		)	
)
member CntWithDatesEqual as
	sum(Dates,[Measures].[Internet Order Count])
SELECT {CntWithDatesEqual} on 0
 FROM
[Adventure Works]
where
[Ship Date].[Calendar Year].&[2001]

SELECT {[Measures].[Internet Order Count]} on 0,
FILTER
(
	NONEMPTY(
		[Date].[Calendar].[Date]*
		[Ship Date].[Calendar].[Date],
		[Measures].[Internet Order Count]
	)
	,
	[Date].[Calendar].currentmember.membervalue
	= [Ship Date].[Calendar].currentmember.membervalue
)	on 1 FROM
[Adventure Works]
where
[Ship Date].[Calendar Year].&[2001]

I have made the ship date same as order date in the AW for first few data and reprocessed the cube.

Hi,

Use the MDX query like the following:

select [Measures].[Internet Order Count] on 0,
filter(
     NonEmpty([Date].[Calendar].[Date] *[Ship Date].[Calendar].[Date],[Measures].[Internet Order Count]),
     [Date].[Calendar].currentmember.parent.name = [Ship Date].[Calendar].currentmember.parent.name
     ) on 1
from [Adventure Works]

thanks,
Jerry

• Marked as answer by Challen FuModerator Thursday, March 17, 2011 9:02 AM

I am getting below error:

 

The NONEMPTY function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

 

Any input is greatly appreciated!!

 

Thanks

Ashay

I am also getting same error.

Kindly suggest the steps.