Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

How to calculate someones age

  • 17 July 2018
  • 2 replies
  • 664 views
A

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

2 replies

VP1

This is incorrect as written. Returns ERROR: column "year" does not exist

xin
Rank
Userlevel 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

Reply


Terms of Service