I am using icCube's Sales cube to learn MDX. I would like to write an MDX query to show the revenue from sales in 2009 for those countries where the revenue from sales in 2010 was over $80,000. I tried the following, but it returned an empty table with only one column ([Amount]):
WITH
SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [Country].members on 0, [Amount] on 1
from (select [myset] on 0, [Amount] on 1 from [sales])
where [2009]
Just use
WITH
SET [myset] AS Filter([Country].members, ([Measures].[Amount], [2010])>80000)
select [myset] on 0, [Amount] on 1
from [sales]
where [2009]
In MDX, this type of query does not need a subselect or WHERE
. And you even could omit the set myset
, writing
select Filter([Country].members, ([Measures].[Amount], [2010])>80000) on 0,
[Amount] on 1
from [sales]
where [2009]
I tried a similar query on Adventure works, and it shows All Customers, Australia, Canada, and US, but not France, Germany, and UK:
SELECT Filter([Customer].[Country].Members,
([Measures].[Internet Sales Amount], [Date].[Calendar Year].&[2006]) > 600000
)
ON 0,
{[Measures].[Internet Sales Amount]}
ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2008]