CODEWITHSUNDEEP
google-analyticslooker-studio
hk100
hk100

Reputation: 21

how to calculate age group in google data studio

I want to have a calculated field for the age group. I have below code but I keep receiving the error message: Failed to create field. Please try again later. 

CASE
WHEN AGE < 19 and age > 0 THEN "BELOW 18"
WHEN AGE > 18 AND AGE < 26 THEN "18 ~ 25"
WHEN AGE > 25 AND AGE < 31 THEN "26 ~ 30"
WHEN AGE > 30 AND AGE < 36 THEN "31 ~ 35"
WHEN AGE > 35 AND AGE < 41 THEN "36 ~ 40"
WHEN AGE > 40 THEN "ABOVE 40"
when age is NULL THEN "Not Provided"
else "Not Provid"
END

where age is calculated as below

FLOOR(DATE_DIFF(created_at,birthday)/365)

can anyone help me with this? thanks a lot!

Upvotes: 2

Views: 3122

Answers (1)

Daniel Alejandro
Daniel Alejandro

Reputation: 41

I know it's late but i did something similar and i hope it could help you or someone, and what i did was:

Age:

DATETIME_DIFF(TODAY('your timezone'),birth_date,year)

Where the timezone you can find it here just use the TZ database name

And for the CASE scenario it was similar

case
when Age >= 0 and Age < 10 then "0-9"
when Age > 9 and Age < 18 then "10-17"
when Age > 17 and Age < 26 then "18-25"
when Age > 25 and Age < 40 then "26-39"
when Age > 39 and Age < 66 then "40-64"
when Age > 64 then "65+"
else "Not provided"
end

Upvotes: 4

Related Questions