6 Replies Latest reply on Oct 11, 2016 1:36 AM by Richard van Dijk

    Calculate age from date of birth

      I'm trying to calculate the age of a person returned in a search from their date of birth by creating a calcuated field and have run into some trouble.

       

      If i create a calculated field titled "Age" with the forumula

       

      DATEDIFF('day', [BirthDate], TODAY()) / 365

       

      I get close, but obviously due to leap years this goes slightly wrong.

       

      What I'm ideally trying to do is replicate the C# code I've written below

       

      DateTime now = DateTime.Today;

      int age = now.Year - DateOfBirth.Year;

      if (DateOfBirth.Value.Date > now.AddYears(-age)) age--;

       

      Return age;

       

      I'm having trouble doing this in the calculated field dialog. If I do the below:

       

      Create two parameters, year and day

       

      write the code

      Year = DATEDIFF('year', [BirthDate], TODAY())

      Day = DATEDIFF('day', [BirthDate], TODAY())

       

      I get a syntax error on my second line. I've searched but can't appear to find any requirement for an endline character. Clearly I'm missing something here but I'm puzzled as to what. Any help would be apreciated.

        • 1. Re: Calculate age from DOB
          Alex Kerin

          What is your full calculation? You need an end after an if statement.

          • 2. Re: Calculate age from DOB

            Hi Alex,

             

            Thanks for your response. I believe you're referring to the C# code I posted? (I'm aware this won't work in a tableau calculated field but I'm simply using it to illustrate what I'm trying to achieve)

             

            If you are referring to the C# code then what I have typed above is syntactically valid, it might be a little clearer expressed as follows however:

            if (DateOfBirth.Value.Date > now.AddYears(-age))

            {

            age--;

            }

             

            I'm trying to perform the same calculation in Tableau as I have in the C# code. (Sorry if that isn't very helpful but C# is my preferred language and I found it the most concise way to express my intended calculation)

             

             

            My intended full calculation will take a date of birth and return the persons age in years

            • 3. Re: Calculate age from DOB
              Marc Engle

              HI Tom,

                   Will something like the below work?  It appears to work on my side in Tableau, but I didn't test specifically for a leap year case.  This would be for the Age calculation to reproduce, at least logically, what you have in c# code above.

               

              IF [DOB]>DATEADD('year',-DATEDIFF('year',[DOB],TODAY()),TODAY()) THEN

                  DATEDIFF('year',[DOB],TODAY())-1

              ELSE

                  DATEDIFF('year',[DOB],TODAY())

              END

               

              Regards,

              Marc

              2 of 2 people found this helpful
              • 4. Re: Calculate age from DOB

                Hi Marc,

                 

                Thanks very much. This has worked perfectly! You've saved me a lot of head scratching!

                 

                Kind regards

                Tom

                • 5. Re: Calculate age from date of birth
                  Nicholas Kamenos

                  What would need to be done to calculate age based on a fixed day? Suppose one is doing historical reporting, using TODAY() would not be accurate.

                   

                  Can a mm/dd/yyyy be substituted for TODAY ()?

                  • 6. Re: Calculate age from date of birth
                    Richard van Dijk

                    To get Age based on a given Reporting Date use the following:

                     

                    IF [DoB] > DATEADD('year',-DATEDIFF('year',[DoB],[Reporting Date]),[Reporting Date]) THEN

                     

                        DATEDIFF('year',[DoB],[Reporting Date])-1

                     

                    ELSE

                     

                        DATEDIFF('year',[DoB],[Reporting Date])

                     

                    END

                     

                    In your case [Reporting Date] could be a calculated field with DateParse, e.g. DATEPARSE("mm/dd/yyyy","10/04/2016"), or parameter driven using MAKEDATE([Year],[Month],[Day]).

                    1 of 1 people found this helpful