none
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

Answers

  • 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

    From [Adventure Works]

     

     

    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

    From [Adventure Works]

     

     

    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