I have a scenario to calculate Age in years from DOB which I am doing using Age(Today(),DOB). The oracle source date format is 'DD-MMM-YY' & datatype is NVARCHAR2(100).
While using Age(Today(),DOB) expression I am able to get correct years except DOB dates below 01-JAN-73 as qliksense is reading 73(YY) as 2073 and same for 72,71,70 etc as 2072,2071,2070 because of which is showing in negative like -49,-48 so on..
I tried to fix date format within qliksense hence the format is changing but year issue is still there and tried below logics:
1- Date format: date(date#(DOB,'DD-MMM-YY'),'DD/MM/YYYY') AS DOB_NEW
2- Date format_2: date(date#(DOB,'DD-MMM-YY'),'M/D/YYYY') AS DOB_NEW2
3- IF (Date format: date(date#(DOB,'DD-MMM-YY'),'DD/MM/YYYY') > '01/01/2026' ,
date(date#(DOB,'DD-MMM-YY'),'DD/MM/19YY') , date(date#(DOB,'DD-MMM-YY'),'DD/MM/YYYY') ) (Logic - to fix the year issue but still dates below 01-JAN-73 showing years as 2073 which should be 1973)
Interesting issue, are you able to get the numeric date values from oracle? That should clear this issue up. If not you could try updating the dates in the script to display as 'DD-MM-YYYY' before apply the age function so Qlik knows the correct format.
The issue isn't related to age() or date#() else to the fact that the year-information isn't complete - without an included century the date-conversion must make a guess. If I remember old community stuff right its hard-coded in the relevant library and goes around 50 years in this and in that direction (AFAIK not from today else the Qlik or library release date or similar).
IMO any attempt to adjust this logic isn't sensible else I suggest to implement a check-logic in regard to your data-set. There are surely various information included which could be used to check the century.
A simpler way may be to apply something like this:
