locked
PrevMember works fine on a calc membe, until filtering member off another dimension. RRS feed

  • Question

  • This calc works.

    SUM(
    EXCEPT (
                         [d Soda].[Soda Sk].[Soda Sk] ,
                   { [d Soda].[Soda Sk].&[-1] }
    )
    ,[Measures].[Soda Transaction Count]
    )

    this calc also works showing prior month.

    (
     [Measures].[Soda Transaction Count]
    , [d Date].[date].PREVMEMBER
    )

    How to get Date's PrevMember works and filtering out one dimension?

    It fails when I want to filter out the Soda.&[0] on the PrevMember.

    AGGREGATE (
    EXCEPT (
                         [d Soda].[Soda Sk].[Soda Sk] ,
                   { [d Soda].[Soda Sk].&[-1] }
    )
    , [Measures].[Soda Transaction Count]
    , [d Date].[date].PREVMEMBER
    )


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Tuesday, September 3, 2013 11:23 PM

Answers

  • The second argument of an Aggregate or Sum function takes an expression that can be evaluated as a number. This is generally a tuple expression. You're trying to pass two members rather than a tuple. To create a valid expression, just reformulate your last expression so that your passing a tuple formed by [d Date].[Date].PrevMember and [Measures].[Soda Transaction Count] as shown below.

    AGGREGATE (
    	EXCEPT (
    		{ [d Soda].[Soda Sk].[Soda Sk].Members },
                    { [d Soda].[Soda Sk].&[-1] }
    	),
    	(
    		[Measures].[Soda Transaction Count], 
    		[d Date].[date].PREVMEMBER
    	)
    )
    
    HTH, Martin


    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    • Marked as answer by light_wt Wednesday, September 4, 2013 1:50 PM
    Wednesday, September 4, 2013 12:57 AM

All replies

  • The second argument of an Aggregate or Sum function takes an expression that can be evaluated as a number. This is generally a tuple expression. You're trying to pass two members rather than a tuple. To create a valid expression, just reformulate your last expression so that your passing a tuple formed by [d Date].[Date].PrevMember and [Measures].[Soda Transaction Count] as shown below.

    AGGREGATE (
    	EXCEPT (
    		{ [d Soda].[Soda Sk].[Soda Sk].Members },
                    { [d Soda].[Soda Sk].&[-1] }
    	),
    	(
    		[Measures].[Soda Transaction Count], 
    		[d Date].[date].PREVMEMBER
    	)
    )
    
    HTH, Martin


    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    • Marked as answer by light_wt Wednesday, September 4, 2013 1:50 PM
    Wednesday, September 4, 2013 12:57 AM
  • Thank you, Martin.  It reinforce what I needed.

    On a side note, for other lost souls out there, I had to add Biz Intelligence on [d Date].


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Wednesday, September 4, 2013 1:51 PM