7 Replies Latest reply on Feb 10, 2015 4:19 PM by philmodjunk

    Age in Years

    Annette

      Title

      Age in Years

      Post

      Hi all, appreciate any help very frustrated at this point as nothing is working.

      I'm trying to get an age of a person as of a given date.

      I have a global field where I put a date in and this is saved in a variable ...  $$EndDate

      I would like to find out the age of a person from their DOB to $$EndDate.  All the ways I try at this point have given me crazy numbers or negative numbers.

       

      So....if someones birthday was 06/02/10 and $$EndDate is 06/02/15 then result would be 5

      If DOB was 01/03/10 and $$EndDate is 06/02/15 then result would be 4.

      Thanks!!!

        • 1. Re: Age in Years
          philmodjunk

          Let (  [ B = YourTable::BirthDateField ;
                      T = $$EndDate 
                   ] ;
                      Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
                )

          Substitute your birthdate field in place of "YourTable::BirthDateField" and make sure that this is a field of type date.

          • 2. Re: Age in Years
            philmodjunk

            Made a major typo adapting a standard calc I use to refer to your variable date. I fixed it here in the forum, but if you are reading your email to see this, the version in your email will be incorrect.

            • 3. Re: Age in Years
              Annette

              Hi, it still doesn't seem to be working.  I get a negative number.  I have written:

              Let (  [ B = ClientDOB ;
                          T = $$EndDate
                       ] ;
                          Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
                    )

              The ClientDOB field is a date field, but it's looking up this info from a date field on another table, does that interfere with this?

               

               

               

              • 4. Re: Age in Years
                Annette

                As an example I have a record where the DOB is 22/10/10 and $$EndDate (01/02/15) gives me a result of -2011.

                • 5. Re: Age in Years
                  philmodjunk

                  When I test it, I get a result of 4.

                  I tested it with the following expression in my dataviewer:

                  Let (  [ ClientDOB = GetAsDate ( "10/22/10" ) ;
                              B = ClientDOB ;
                              T = GetAsDate ( "02/01/15" ) // $$EndDate
                           ] ;
                              Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
                        )

                  Since my locality settings expect dates to be in an MM/DD/YY format, I swapped the month and day values inside my quoted strings in order to get accurate, valid dates for the test.

                  • 6. Re: Age in Years
                    Annette

                    UGH!  THANKS!!!  The problem was not in the calculation (as you knew already) but that it was indexed and saved the results from when I had the wrong calculation being used.  Once I ticked do not store results, recalculate when needed it showed me the right info. 

                     

                    I still haven't wrapped my head around when that do not store tick box should be used.  I would think any calculation where the results change regularly such as this but when I have scripts for reports that include those fields they seem to take forever to run until I untick that box then they run in seconds.

                    • 7. Re: Age in Years
                      philmodjunk

                      A stored calculation field will only update if a field referenced in the calculation is modified. Modifying a variable or a get function such as Get ( CurrentDate ) returning a different date will not trigger an update.