locked
Intersect of The Stores which has sales on both Date parameter values RRS feed

  • Question

  • I'm trying to give the number of stores which has sales on both dates that I give as a parameter. I know I will use Intersect but I couldn't return the stores that has sales on left side and right side of time line.
    BI Developer
    Saturday, January 8, 2011 12:03 AM

Answers

  • Hi,

    I'm sure if you google you will find examples for this, but anyhow this is what I've come up with.

    Note that there are alternative versions for the definitions of sets that contain the members that satisfy the condition, and there are the versions of calculated measures to count the members that satisfy that condition.

    with
    
    set dates as 
    {
    	[Date].[Calendar].[Date].&[20020105]
    	,
    	[Date].[Calendar].[Date].&[20020109]
    }
    
    // Can only be used for 2 days/periods
    member CountOfProducts_Fixed2Days as
    SUM
    (
    	existing([Product].[Product Categories].[Product])
    	,Iif
    	(
    		IsEmpty((dates.item(0), [Measures].[Internet Sales Amount]))
    		OR
    		IsEmpty((dates.item(1), [Measures].[Internet Sales Amount]))
    		,NULL -- some date is empty
    		,1 -- passes, so we count this product
    	)
    )
    
    // Can only be used for any number of days/periods
    member CountOfProducts_FlexibleDays as
    SUM
    (
    	existing([Product].[Product Categories].[Product])
    	,
    	Iif
    	(
    		StrToVal
    		(
    			Generate
    			(
    				dates
    				,'IsEmpty((' + dates.current.Unique_Name + ', [Measures].[Internet Sales Amount]))'
    				,' OR '
    			)
    		)
    		,NULL -- some date is empty
    		,1 -- passes, so we count this product
    	)
    )
    
    set Products_Filter as
    Filter
    (
    	existing([Product].[Product Categories].[Product])
    	,NOT
    	(
    		IsEmpty((dates.item(0), [Measures].[Internet Sales Amount]))
    		OR
    		IsEmpty((dates.item(1), [Measures].[Internet Sales Amount]))
    	)
    )
    
    set Products_Interset as
    Intersect
    (
    	extract
    	(
    		NonEmpty
    		(
    			[Product].[Product Categories].[Product]
    			*
    			dates.item(0)
    			,[Measures].[Internet Sales Amount]
    		)
    		,[Product].[Product Categories]
    	)
    	,
    	extract
    	(
    		NonEmpty
    		(
    			[Product].[Product Categories].[Product]
    			*
    			dates.item(1)
    			,[Measures].[Internet Sales Amount]
    		)
    		,[Product].[Product Categories]
    	)
    )
    
    

     

    Explanation or discussion available upon request :)

    HTH,

    Hrvoje Piasevoli

    • Marked as answer by Erdem Zengin Monday, January 10, 2011 1:43 PM
    Saturday, January 8, 2011 1:34 AM

All replies

  • Hi,

    I'm sure if you google you will find examples for this, but anyhow this is what I've come up with.

    Note that there are alternative versions for the definitions of sets that contain the members that satisfy the condition, and there are the versions of calculated measures to count the members that satisfy that condition.

    with
    
    set dates as 
    {
    	[Date].[Calendar].[Date].&[20020105]
    	,
    	[Date].[Calendar].[Date].&[20020109]
    }
    
    // Can only be used for 2 days/periods
    member CountOfProducts_Fixed2Days as
    SUM
    (
    	existing([Product].[Product Categories].[Product])
    	,Iif
    	(
    		IsEmpty((dates.item(0), [Measures].[Internet Sales Amount]))
    		OR
    		IsEmpty((dates.item(1), [Measures].[Internet Sales Amount]))
    		,NULL -- some date is empty
    		,1 -- passes, so we count this product
    	)
    )
    
    // Can only be used for any number of days/periods
    member CountOfProducts_FlexibleDays as
    SUM
    (
    	existing([Product].[Product Categories].[Product])
    	,
    	Iif
    	(
    		StrToVal
    		(
    			Generate
    			(
    				dates
    				,'IsEmpty((' + dates.current.Unique_Name + ', [Measures].[Internet Sales Amount]))'
    				,' OR '
    			)
    		)
    		,NULL -- some date is empty
    		,1 -- passes, so we count this product
    	)
    )
    
    set Products_Filter as
    Filter
    (
    	existing([Product].[Product Categories].[Product])
    	,NOT
    	(
    		IsEmpty((dates.item(0), [Measures].[Internet Sales Amount]))
    		OR
    		IsEmpty((dates.item(1), [Measures].[Internet Sales Amount]))
    	)
    )
    
    set Products_Interset as
    Intersect
    (
    	extract
    	(
    		NonEmpty
    		(
    			[Product].[Product Categories].[Product]
    			*
    			dates.item(0)
    			,[Measures].[Internet Sales Amount]
    		)
    		,[Product].[Product Categories]
    	)
    	,
    	extract
    	(
    		NonEmpty
    		(
    			[Product].[Product Categories].[Product]
    			*
    			dates.item(1)
    			,[Measures].[Internet Sales Amount]
    		)
    		,[Product].[Product Categories]
    	)
    )
    
    

     

    Explanation or discussion available upon request :)

    HTH,

    Hrvoje Piasevoli

    • Marked as answer by Erdem Zengin Monday, January 10, 2011 1:43 PM
    Saturday, January 8, 2011 1:34 AM
  • Hi Hrvoje,

    I think when I use the last one with Count function, It will satisfy my needs.

    Thanks for help.


    BI Developer
    Monday, January 10, 2011 1:43 PM