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

# Create Age group

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

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

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

In your case it would be

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:

1 of 1 people found this helpful
• ###### 3. Re: Create Age group

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

Thank you Joe for the solution, really appreciated.

GG

• ###### 5. Re: Create Age group

Thank you again, It worked pretty good for me.

GG

• ###### 6. Re: Create Age group

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

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

Hi Gurpreet,

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

• ###### 9. Re: Create Age group

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('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