How to calculate someones age

calculations
bucket
lookml

(Ayy) #1

Hi,

I’ve got a range of data that is only filtered by the users D.O.B in the format Y/M/D, since it’s a lot of data I need to clear it up so it shows their age. Is there a way to calculate the users age from their D.O.B to now? I’m unsure on queries and how to filter this particular task. So for example one of the dates is 1992/08/29 and to work out their actual age it would mean their birthday this year would be 2018/08/29. I’d want to know what the number is of that age range which of course would be the users age. I want to do this for all users.

Also is there any manual way to group age range? E.g. 24-29 30-35 etc

Thank you in advance


(Xin Bao) #2

Hi Ayy,

The best way to calculate DOB is to creating an age dimension using case when in the sql.

Here’s an example I used in my analysis on calculating the number of years of project:

dimension: project_years_use_casewhen {
    type: number
    sql: case 
    when extract(month from DATE now()) > extract(month from ${project_posted_date})
    then DATE_DIFF(now(), ${project_posted_date}, year)
    when extract(month from DATE now()) < extract(month from ${project_posted_date})
    then DATE_DIFF(now(), ${project_posted_date}, year)-1
    when extract(month from DATE now()) = extract(month from ${project_posted_date}) and 
    extract(day from DATE now()) < extract(day from ${project_posted_date})
    then DATE_DIFF(now(), ${project_posted_date}, year)-1
    when extract(month from DATE now()) = extract(month from ${project_posted_date}) and 
    extract(day from DATE now()) >= extract(day from ${project_posted_date})
    then DATE_DIFF(now(), ${project_posted_date}, year)
    end;;
  }

The above is based on bigquery standard, you have to adjust it based on your sql dialect.

Regarding group age range, you can use “tier” to bucket the age.

If you have any further questions, please feel free jump on chat, we can provide more detailed support there.

Best,
Xin