Calculating age based on Date of birth for current and previous years.

[Manmeet]

Hi All,

I need to calculate Age based on Date of birth("DOB") column, but it should change dynamically if we make a selection for any previous "Fiscal year" or "Fiscal month" in Qlik sense.

Looking forward to your suggestions.

Thanks & Regards,

Manmeet Dhawan

[G3S]

Can you please provide screenshot/ example of expected output?

[Mark_Little]

HI @Manmeet 

Try something like 

IF(GetFieldSelections("Fiscal year") = 0 AND GetFieldSelections("Fiscal month") = 0,

Today() - "DOB",

DATE( '01/'&GetFieldSelections("Fiscal month")&'/'& GetFieldSelections("Fiscal year")) - "DOB"

)

Probably have to play with the date section to get the date formatted correctly 

[Manmeet]

Hi Thanks for the response,

So, the expected output is like if my current age is 26.5(26 years 5 months) as on current year and month, I should get the same as my age in the "Age"  column, But it should change as per the selection of  previous year /month.

Ex: for 2020 same month if i check it should provide age as on that year/month i.e. 24.5 .

I hope i was able to explain it properly.

[Manmeet]

Thanks for your response, Trying to implement this logic not getting any result...

Can you please explain it with the help of a screenshot??

[PrashantSangle]

Hello,

Use age() in your expression.

try like age(date(max(your_date_field)),date_of_birth_field)

Regards,

Prashant Sangle

[Manmeet]

Thanks for your response,Using the above code also is not giving the correct results, I'm currently using below code to calculate age, it calculates correct for current year, But doesn't change when we select any other year/ month. 

Num((Interval(date(Date#(today(),'MM/DD/YYYY'),'MM/DD/YYYY')
-Date(Date#(DOB,'MM/DD/YYYY'),'MM/DD/YYYY'),'DD')/365),'#,##.00')
as Age;

Kindly suggest.

[PrashantSangle]

Hi,

which datefield you are using to select date & year?

It will not gave you the answer which you are looking for because you are doing

today - DOB

You have to do datefield(which_you_are_selecting) - DOB

Also, can you explain what is wrong you are getting with my above solution. Only by saying it is not giving correct result won't help us to understand the issue.

Regards,

Prashant Sangle

[Otto65]

The key issue here is that you're using Today() in your calculation, which always returns the current system date regardless of your fiscal year selection. As Prashant pointed out, you need to reference the actual date field that users are selecting.

For a dynamic age calculation that responds to fiscal year/month selections, you'll want to use something like:

text

Age(
    Date(GetFieldSelections("Fiscal year") & '-' & GetFieldSelections("Fiscal month") & '-01'),
    DOB
)

This uses the Age() function which handles the year/month/day boundary logic automatically, and it references the selected fiscal period instead of today's date.

If you need the age in decimal format (like 26.5 for 26 years 5 months), you can modify it:

text

Num(
    (Date(GetFieldSelections("Fiscal year") & '-' & GetFieldSelections("Fiscal month") & '-01') - DOB) / 365.25,
    '#,##0.0'
)

For verifying your calculation logic, I've found it helpful to cross-check with an external tool. I often use agecalculatorsa.com to validate the output when testing complex date logic, especially for edge cases like birthdays near leap years or when dealing with partial months.

The Age() function is generally the most reliable approach in Qlik because it handles month/day boundaries correctly. The decimal method using 365.25 gives you a close approximation but may have slight rounding differences for exact dates.