I have the following SQL query which I am trying to convert into MDX:
select avg(skucount)
from
(
SELECT count(distinct [SKUCode]) as skucount
--,[SHOPCODE_WITHOUT_DIST]
FROM [HFPL_DW].[dbo].[FactSecondarySales]
where DISTCODE in
(
SELECT [DISTRIBUTORCODE]
FROM [HFPL_DW].[dbo].[DimDistHierarchy]
where REGION = 'KARACHI'
)
and month(saledate) = 7 and year(saledate) = 2018
group by [SHOPCODE_WITHOUT_DIST]
) as inner_query
The inner query returns the count of SKU saled on each shop(which is fulfilled by using "Group by ShopCode")
First I am trying to convert the inner query to MDX, I have tried the following:
WITH MEMBER [Measures].[SKU Count] AS
COUNT( NonEmpty( { [Product Hierarchy].[SKU].[SKU].Members }, ( [Shop Hierarchy].[SHOPCODE WITHOUT DIST] ) ) )
SELECT
{
[Measures].[SKU Count]
} ON COLUMNS,
NonEmpty(
{ [Product Hierarchy].[SKU].[SKU].Members },
([Shop Hierarchy].[SHOPCODE WITHOUT DIST] )
) ON ROWS
FROM
[Consolidated Sales]
where(
[Time Analysis].[Month].&[2018-07-01T00:00:00],
[Distribution Hierarchy].[DISTRIBUTORCODE].&[1002]
)
It is not returning anything.
Kindly help me acheive the desired output of the Average SKUs saled(The outer query), the number of SKUs saled per shop (inner query)