BusinessObjects Board

Calculating age accurately

This is a bit of a perennial problem for me as I’m dealing with social care date and the supplier universe only provides ‘Current Age in Years’, ‘Age at Previous FY End’ and ‘Age at Current FY End’ (which immediately increments by 1 if you run a report in the new FY and want data from the previous FY). This means 2 reports using current FY dates and objects in one and previous FY dates and objects in the other with the resulting maintenance implications.

I’ve been using a floor(((MonthsBetween() calculation)+1)/12) using date of birth and either the date of death or the FY end date if there is no date of death or the date of death is after the FY end date. With our latest BO patch, we’ve now got YearPeriod which makes DatesBetween() viable, but I’ve noticed that YearPeriod returns a whole or part year so isn’t always accurate. I can’t see any toggle in the function that allows only complete years.

Does anyone have a good algorithm to calculate people’s ages in BO at report level (we don’t have access to the universe)? It’s important for me because of statutory reporting needing accurate ages to apportion age bands.

=If CurrentDate() > RelativeDate([Birthday];DatesBetween([Birthday]; CurrentDate(); YearPeriod);YearPeriod) Then Year(CurrentDate()) - Year([Birthday]) Else Year(CurrentDate()) - Year([Birthday]) -1