SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: 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

A: Example of such query:

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]);

Result will be:

	Internet Sales Amount	CustomersW/FirstSales
Accessories	$254.23	3
Bikes	$12,199.16	5
Clothing	$112.97	3
Components	(null)	0

Done.