5 Replies Latest reply on Oct 26, 2016 12:15 PM by Gurpreet Singh Goraya

# Help with Age Calculation

Hello All,

I am seeing a strange behaviour with age calculation field. I have created a calculated field for the age calculation, but my issue is one of the variables is not counting age accordingly. As you will see in age 26-30 category clients, Tableau is counting over 100 years. I would appreciate if someone helps me on that. Please see the screenshot for details. Thanks in advance.

• ###### 1. Re: Help with Age Calculation

Try changing your formula so it mirrors something like this:

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"

.

.

.

and so on. That should work for you.

1 of 1 people found this helpful
• ###### 2. Re: Help with Age Calculation

Thank you Benjamin. It works for me. But, I need to know what was wrong with my formula?

Gurpreet

• ###### 3. Re: Help with Age Calculation

In your calculation, you have your numbers transposed for the 26 to 30 range.  You need to reverse it.   Everything else looks fine.  Hence it is failing the if test just for that range.

1 of 1 people found this helpful
• ###### 4. Re: Help with Age Calculation

Srinidhi is right. Essentially, that line is testing if the year of birth is simultaneously greater than or equal to 1990 and less than or equal to 1986, which is obviously impossible. So all those records that would have been classified in that range are instead getting caught in the Else "Over 100" group.

I do want to stress the fact that DATEDIFF is a more accurate way of calculating age, though. Consider the case of someone who was born on December 1, 2000. Their actual age is 15 right now, as they won't turn 16 until December 1, 2016. DATEDIFF would correctly categorize this person as Under 16. However, your original calculation would incorrectly put this person in the 16-20 group, since 2000 is equal to 2016 minus 16.

• ###### 5. Re: Help with Age Calculation

Thank you Srinidhi, I didn't realize that I have reversed numbers.

Gurpreet