Following up on this since a few people asked about the month/day comparison I mentioned. To handle whether the birthday has already passed this year, you extend the same logic: text | eval birth_year = tonumber(substr(APPLICANT_DOB, 1, 4))
| eval birth_month = tonumber(substr(APPLICANT_DOB, 6, 2))
| eval birth_day = tonumber(substr(APPLICANT_DOB, 9, 2))
| eval cur_year = tonumber(strftime(now(), "%Y"))
| eval cur_month = tonumber(strftime(now(), "%m"))
| eval cur_day = tonumber(strftime(now(), "%d"))
| eval age = cur_year - birth_year - if((cur_month < birth_month) OR (cur_month = birth_month AND cur_day < birth_day), 1, 0) This gives the exact completed years, including the case where the birthday hasn't occurred yet this calendar year. One thing to watch: substr indexing assumes the DOB string is always in YYYY-MM-DD format. If any records come through with a different format or a missing day component, those rows will silently produce wrong values. Worth adding a length check or a match() guard before the calculation if your data isn't clean.
... View more