none
MDX : Simple Substraction

    Question

  • Greetings Team !

    Can some please advise how do i go about substracting a Member with current dimension  to calucalte age here ?

    WITH  MEMBER [Admit Date] AS 
    HEAD( FILTER([Time].[DATE].children , [Measures].[Admission Count] > 0 )).item(0).member_value
    
    SELECT 
    		{ 
    			[Admit Date]
    	    } ON 0 , 
    	    
    		(  [Patient].[Patient].&[X],
    		   [Patient].[Patient Year of Birth].[Patient Year of Birth].Members
    		 ) ON 1 
    FROM   [<<Cube Name>>]	

    Current OutpPut

                  AdmitDate

    X 1945    7/15/2004

    I'm looking or an calculated Member AGE with substracts 1945 from 2004 .

                  AdmitDate        AGe

    X   1945   7/25/2004     59 ( 2004 - 1945 )

    Thanks in advace for your help !


    Rajkumar Yelugu

    • Changed type Rajkumar Yelugu Monday, September 16, 2013 7:06 AM Question Instead
    Friday, September 13, 2013 2:10 PM

Answers

  • Here's an example using AdventureWorks:

    WITH
    	MEMBER [Measures].[BirthDate] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,[Customer].[Customer].CurrentMember.Properties("Birth Date")
    		)
    	MEMBER [Measures].[CurrentDate] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,now()
    		)
    	MEMBER [Measures].[Age] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,VBA!DATEDIFF("yyyy",[Measures].[BirthDate], now())
    		)
    SELECT
    	NON EMPTY {
    		[Measures].[Internet Sales Amount],
    		[Measures].[BirthDate],
    		[Measures].[CurrentDate],
    		[Measures].[Age]
    	} ON 0,
    	NON EMPTY {
    		[Customer].[Customer].[Customer]
    	} ON 1
    FROM
    	[Adventure Works]
    WHERE
    	[Date].[Calendar].[Date].&[20070218]

    In this case BirthDate is a member property in the customer dimension...your's may be a regular attribute...in which case you can probably just grab it via MemberValue function.

    Also, in the example above, the age is being calculated as the difference between the current date and the BirthDate...but in your example, instead of using current date, you could just use the "Admit Date"

    hth

    Edit: oh and btw this isn't a very precise way to calculate age because it doesn't factor in the date or month...


    BI Developer and lover of data (Blog | Twitter)



    • Edited by BillAnton Friday, September 13, 2013 4:03 PM
    • Marked as answer by Rajkumar Yelugu Monday, September 16, 2013 7:06 AM
    Friday, September 13, 2013 4:02 PM

All replies

  • Here's an example using AdventureWorks:

    WITH
    	MEMBER [Measures].[BirthDate] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,[Customer].[Customer].CurrentMember.Properties("Birth Date")
    		)
    	MEMBER [Measures].[CurrentDate] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,now()
    		)
    	MEMBER [Measures].[Age] AS
    		IIF(
    			ISEMPTY([Measures].[Internet Sales Amount])
    			,NULL
    			,VBA!DATEDIFF("yyyy",[Measures].[BirthDate], now())
    		)
    SELECT
    	NON EMPTY {
    		[Measures].[Internet Sales Amount],
    		[Measures].[BirthDate],
    		[Measures].[CurrentDate],
    		[Measures].[Age]
    	} ON 0,
    	NON EMPTY {
    		[Customer].[Customer].[Customer]
    	} ON 1
    FROM
    	[Adventure Works]
    WHERE
    	[Date].[Calendar].[Date].&[20070218]

    In this case BirthDate is a member property in the customer dimension...your's may be a regular attribute...in which case you can probably just grab it via MemberValue function.

    Also, in the example above, the age is being calculated as the difference between the current date and the BirthDate...but in your example, instead of using current date, you could just use the "Admit Date"

    hth

    Edit: oh and btw this isn't a very precise way to calculate age because it doesn't factor in the date or month...


    BI Developer and lover of data (Blog | Twitter)



    • Edited by BillAnton Friday, September 13, 2013 4:03 PM
    • Marked as answer by Rajkumar Yelugu Monday, September 16, 2013 7:06 AM
    Friday, September 13, 2013 4:02 PM
  • Thanks for your time  GentleMan  .

    Rajkumar Yelugu

    Monday, September 16, 2013 7:06 AM