locked
MDX - Count function not returning correct numbers RRS feed

  • Question

  • Hi,

    I am trying to get number of months having Internet sales amount < 1500000 from Adventure works cube,

    MDX1 :: select Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000) on 0 from [Adventure Works];

    MDX1 gives me result as below, which means every year have one month only having  Internet sales amount < 1500000

    MDX2 :: with member [No of Months having Internet Sales Amount < 1500000] as
    count(Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000),excludeempty)
    select [No of Months having Internet Sales Amount < 1500000] on 0 from [Adventure Works];

    MDX2 give me 5 as result.

    MDX3 ::with member [No of Months having Internet Sales Amount < 1500000] as
    count(Filter(([Measures].[Internet Sales Amount],[Date].[Calendar].[Calendar Quarter]),[Measures].[Internet Sales Amount]<1500000),excludeempty)
    select [No of Months having Internet Sales Amount < 1500000] on 0,[Date].[Calendar].[Calendar Year].members on 1 from [Adventure Works];

    MDX3 returns me all year with same count as 5 which should be 1 actually. Can someone help me out getting the desired result.

    Thanks in advance !!

    Friday, July 27, 2012 10:16 AM

Answers

  • Here you have query, which gives numer of months having Internet saless less than 1500000. It Lists all the periots up to the Month Level, so it is easy to verify the correctness.

    WITH
    	MEMBER [Months Having Less Than 1500000] AS COUNT(FILTER(EXISTING [Date].[Calendar].[Month].MEMBERS, [Measures].[Internet Sales Amount] < 1500000)) 
    SELECT
    	{
    		[Measures].[Internet Sales Amount],
    		[Months Having Less Than 1500000]
    	} ON COLUMNS,
    
    	{
    		DESCENDANTS([Date].[Calendar].[All Periods], [Date].[Calendar].[Month], SELF_AND_BEFORE)
    	} ON ROWS
    FROM [Adventure Works]

    It is Important to use EXISTING keyword inside the Filter so only members belonging to current processed scope are taken in mind.
    • Marked as answer by Jack_B24 Friday, July 27, 2012 11:04 AM
    Friday, July 27, 2012 10:31 AM

All replies

  • Here you have query, which gives numer of months having Internet saless less than 1500000. It Lists all the periots up to the Month Level, so it is easy to verify the correctness.

    WITH
    	MEMBER [Months Having Less Than 1500000] AS COUNT(FILTER(EXISTING [Date].[Calendar].[Month].MEMBERS, [Measures].[Internet Sales Amount] < 1500000)) 
    SELECT
    	{
    		[Measures].[Internet Sales Amount],
    		[Months Having Less Than 1500000]
    	} ON COLUMNS,
    
    	{
    		DESCENDANTS([Date].[Calendar].[All Periods], [Date].[Calendar].[Month], SELF_AND_BEFORE)
    	} ON ROWS
    FROM [Adventure Works]

    It is Important to use EXISTING keyword inside the Filter so only members belonging to current processed scope are taken in mind.
    • Marked as answer by Jack_B24 Friday, July 27, 2012 11:04 AM
    Friday, July 27, 2012 10:31 AM
  • Excellent … exactly what I want. Thank You !!

    Friday, July 27, 2012 11:06 AM