# Age calculation

### Question

•

Hi,
I have a question about age calculation.
I have two dimension. First one contains information about data of birth:
[Date].[Dob].ALLMEMBERS
Second one contains information when the clients information was registered: [Date].[EntryDate]. ALLMEMBERS.

So I can get age like: AGE = ([Date].[EntryDate]. ALLMEMBERS)- [Date].[Dob].ALLMEMBERS.
I have something like this:

With
Member [Measures].[memberage] as
'datediff("yyyy",[Edcards].[Edc Dob].CurrentMember.MemberValue,NOW()) '

select {[Measures].[ClientCount],[Measures].[memberage]} on columns,
{[Edcards].[Edc Dob].ALLMEMBERS} on rows
from [MyCube]

And It works fine. But I would like to have calculation: for each client date of birth->client age
Do you know how to accomplish this?
Regards
Tomasz

Wednesday, January 02, 2008 6:51 PM

• Hi Tomas

I've tried to replicate it in Adventure Works.

Replace Promotion by Client, Promotion.EndDate by Client.DoB and Date.Date by Date.EntryDate.

I tried to simulate age calculation by calculating days difference between the EndDate attribute of Promotion dimension and the Date attribute of Date dimension.

I suppose there exists a one-to-one relationship between Client and Entry Date. I mean, there exists only one entry date for each client in your data model so as to avoid mixing different entry dates with a particular birth date.

Adventure Works doens't apply this supposal that's why both positive and negative simulated "ages" are returned in query.

The All level es excluded from all dimensions as none of them have date values to calculate from:

Code Block

With member [Measures].[Age] as 'datediff("d",[Promotion].[End Date].currentmember.membervalue,[Date].[Date].currentmember.membervalue)'

Select NON EMPTY [Promotion].[Promotion].[All Promotions].children * [Promotion].[End Date].[All Promotions].children * [Date].[Date].[All Periods].children

On rows,

[Measures].[Age] on columns

Try it and tell me if it is what you want.

Wednesday, January 02, 2008 9:51 PM

### All replies

• Hi Tomas

I've tried to replicate it in Adventure Works.

Replace Promotion by Client, Promotion.EndDate by Client.DoB and Date.Date by Date.EntryDate.

I tried to simulate age calculation by calculating days difference between the EndDate attribute of Promotion dimension and the Date attribute of Date dimension.

I suppose there exists a one-to-one relationship between Client and Entry Date. I mean, there exists only one entry date for each client in your data model so as to avoid mixing different entry dates with a particular birth date.

Adventure Works doens't apply this supposal that's why both positive and negative simulated "ages" are returned in query.

The All level es excluded from all dimensions as none of them have date values to calculate from:

Code Block

With member [Measures].[Age] as 'datediff("d",[Promotion].[End Date].currentmember.membervalue,[Date].[Date].currentmember.membervalue)'

Select NON EMPTY [Promotion].[Promotion].[All Promotions].children * [Promotion].[End Date].[All Promotions].children * [Date].[Date].[All Periods].children

On rows,

[Measures].[Age] on columns

Try it and tell me if it is what you want.

Wednesday, January 02, 2008 9:51 PM
• Could you give an example of the actual results vs.what you want? Also, could you explain the [Edcards].[Edc Dob] attribute - is [Edcards] a client dimension, where [Edc Dob] is an attribute?

Wednesday, January 02, 2008 9:56 PM
• Hi,
I’m sorry, I’ve type wrong query syntax. It should be:

With
Member [Measures].[memberage] as
'datediff("yyyy",[Date].[Dob].CurrentMember.MemberValue,NOW()) '
select {[Measures].[ClientCount],[Measures].[memberage]} on columns,
{[Date].[Dob].ALLMEMBERS} on rows
from [MyCube]

Anyway I was able to recreate solution proposed by Leandro, and it seems ok. Thank you very much –both of you.
Tomasz

Wednesday, January 02, 2008 10:31 PM