Tuesday, March 31, 2015

How can I write MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category?

WITH SET [FirstSales] AS
FILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS
* [Date].[Date].[Date].MEMBERS
, [Measures].[Internet Sales Amount])
AS MYSET,
MYSET.CURRENTORDINAL = 1 or
NOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))
MEMBER [Measures].[CustomersW/FirstSales] AS
COUNT(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),
FORMAT_STRING = '#,#'
SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} ON 0,
[Product].[Product Categories].[Category] ON 1
FROM [Adventure Works]
WHERE ({[Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003]}, [Customer].[Customer Geography].[City].&[Calgary]&[AB]);

No comments:

Post a Comment