# 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 ?

SELECT
{
} ON 0 ,

(  [Patient].[Patient].&[X],
[Patient].[Patient Year of Birth].[Patient Year of Birth].Members
) ON 1
FROM   [<<Cube Name>>]

Current OutpPut

X 1945    7/15/2004

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

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

Rajkumar Yelugu

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

• 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
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 Friday, September 13, 2013 4:03 PM
• Marked as answer by 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
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 Friday, September 13, 2013 4:03 PM
• Marked as answer by 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