Report Portal

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

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 AmountCustomersW/FirstSales
Accessories$254.233
Bikes$12,199.165
Clothing$112.973
Components(null)0

Done.

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.