9 Replies Latest reply on Sep 27, 2018 11:04 AM by Gurpreet Singh Goraya

    Create Age group

    Gurpreet Singh Goraya

      Hello all,

       

      I have a date of a birth field as "DATE" . The date field is recorded in  "mm/dd/yyyy" format. I want to create age group like 11-15 Years, 16-20 years, 21-25 and so on. Please advise. Thanks

       

      GG

        • 1. Re: Create Age group
          cor.bader

          Here is a example of how the calculation would look using super store data:

           

          If YEAR([Order Date]) <= YEAR(TODAY())-3 then "Group 1"

          ELSEIF YEAR([Order Date]) <= YEAR(TODAY())-1 AND YEAR([Order Date]) >= YEAR(TODAY())-3 then "Group 2"

          Else "Other"

          END

          1 of 1 people found this helpful
          • 2. Re: Create Age group
            Joe Oppelt

            To find the number of years between your date and today, use the DATEDIFF() function.

             

            In your case it would be

             

            DATEDIFF('year', [your date field], TODAY())


            Output is an integer for number of years.

             

            Create a calc like that.  It will give you a number of years for each row.  Group them by values as you need.

             

            You can find a lot of stuff about date functions here:

             

            Date Functions

            1 of 1 people found this helpful
            • 3. Re: Create Age group
              Gurpreet Singh Goraya

              Thank you for your help. It works great, but I am having difficulty with creating the third field. Thaks again.

               

              GG

              • 4. Re: Create Age group
                Gurpreet Singh Goraya

                Thank you Joe for the solution, really appreciated.

                 

                GG

                • 5. Re: Create Age group
                  Gurpreet Singh Goraya

                  Thank you again, It worked pretty good for me.

                   

                  GG

                  • 6. Re: Create Age group
                    Tiffiny Townsend

                    Hi Gurpreet,

                     

                    I used your example to create age groups, swapping the field in your example for DOB field, to create 10 year age groups.

                     

                    I have am getting an error message "Expected Then to Match IF at the character 0". Am I missing something? Version 10.5.

                     

                    • 7. Re: Create Age group
                      Gurpreet Singh Goraya

                      Hello Tiffiny,

                       

                      Thanks for asking, just to confirm if you have [Patient_DOB] field in Date format. I have encountered the said error a couple of time when I run Oracle SQL query but the only fix happens to be within the query. Thanks

                      1 of 1 people found this helpful
                      • 8. Re: Create Age group
                        Tiffiny Townsend

                        Hi Gurpreet,

                         

                        Thank you for your quick response. Yes, the DOB field is Date format. Lynn

                        • 9. Re: Create Age group
                          Gurpreet Singh Goraya

                          Hello Tiffiny,

                           

                          In the past, I have extracted DateofBirth as number format (19220416) and then converted DateofBirth measure to Date format by using the following LOD:

                           

                          DATEADD('day',[DATEOFBIRTH] % 100 - 1,

                          DATEADD('month', INT(([DATEOFBIRTH] % 10000) / 100) - 1,

                          DATEADD('year', INT([DATEOFBIRTH] / 10000) - 1900,

                          #1900-01-01#)))

                          It converts Number format to Date Format and then used the following calculation for creating age group:

                           

                          IF DATEDIFF('year', [Dateof Birth], TODAY())<16 THEN "Under 16"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=16 AND DATEDIFF('year', [Dateof Birth], TODAY())<=20 THEN "16-20"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=21 AND DATEDIFF('year', [Dateof Birth], TODAY())<=25 THEN "21-25"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=26 AND DATEDIFF('year', [Dateof Birth], TODAY())<=30 THEN "26-30"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=31 AND DATEDIFF('year', [Dateof Birth], TODAY())<=35 THEN "31-35"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=36 AND DATEDIFF('year', [Dateof Birth], TODAY())<=40 THEN "36-40"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=41 AND DATEDIFF('year', [Dateof Birth], TODAY())<=45 THEN "41-45"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=46 AND DATEDIFF('year', [Dateof Birth], TODAY())<=50 THEN "46-50"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=51 AND DATEDIFF('year', [Dateof Birth], TODAY())<=55 THEN "51-55"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=56 AND DATEDIFF('year', [Dateof Birth], TODAY())<=60 THEN "56-60"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=61 AND DATEDIFF('year', [Dateof Birth], TODAY())<=65 THEN "61-65"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=66 AND DATEDIFF('year', [Dateof Birth], TODAY())<=70 THEN "66-70"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=71 AND DATEDIFF('year', [Dateof Birth], TODAY())<=75 THEN "71-75"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=76 AND DATEDIFF('year', [Dateof Birth], TODAY())<=80 THEN "76-80"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=81 AND DATEDIFF('year', [Dateof Birth], TODAY())<=85 THEN "81-85"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=86 AND DATEDIFF('year', [Dateof Birth], TODAY())<=90 THEN "86-90"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=91 AND DATEDIFF('year', [Dateof Birth], TODAY())<=95 THEN "91-95"

                          ELSEIF DATEDIFF('year', [Dateof Birth], TODAY())>=96 AND DATEDIFF('year', [Dateof Birth], TODAY())<=100 THEN "96-100"

                          Else "Over 100"

                          END

                           

                          I noticed that you are missing "DATEDIFF" from your calculations. Thanks

                           

                          GG