Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Converting Age providing negative counts

Hi All,

I have a data of Patients Date of birth in the format DD-MMM-YY that I am converting first into the Age and then creating Age Groups using Class() function, script is below:

LOAD

[PATIENT_BIRTHDATE],

Age(today(),[PATIENT_BIRTHDATE]) as [Patient Age],

Class(Age(today(),[PATIENT_BIRTHDATE]),10,'Age Range') as [Patient Age Group],

The output that I am getting have negative Age counts, which is not correct, DOB I have check and its valid, I am not sure why converted data showing negative Age(s). This is impacting the Age Groups as well that I am creating further.  Please see the below screen shot.

Can someone please help me and troubleshot why this weird Output?

AGE.png

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

It looks like your fault dates are all being mis-interpreted as year 2061 instead of 1961. Can your data source produce 4 digit years?

Otherwise, you will have to provide some rules to determine the century; for example:

Edit:

If([PATIENT BIRTHDATE] > Today(), AddYears([PATIENT BIRTHDATE], -1), [PATIENT BIRTHDATE]) as [PATIENT BIRTHDATE],

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Data I am feeding from an excel , I can change it manually but, that will be cumbersome as I need to change things each time new data comes in excel, Can you help us with a way we can convert things in QS itself at the time of data load?

One more thing it gives positive and correct values for the same date format as well, do you think either all or none should be affected if Date format is an issue?

Thx

AG

jonathandienst
Partner - Champion III
Partner - Champion III

See edit above

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

I presume by 16-Oct-61 you mean 16th Oct in the year 1961.  But I suspect the actual numeric value of your date is 2061.

I have tried these expressions in a Text object:

     age (  today()  , date(date#('16-Oct-61' , 'DD-MMM-YY' )) )               returns -45, the same you got

Whereas :

      age (  today()  , date(date#('16-Oct-1961' , 'DD-MMM-YYYY' )) )     returns 55 which is correct

Anonymous
Not applicable
Author

Hi Jonathon,

I added the statement you specified and it throwing error, "Field Patient Birth Date Not Found"

LOAD

    [PATIENT_BIRTHDATE],

    If([PATIENT BIRTHDATE] > Today(), AddYears([PATIENT BIRTHDATE], -1), [PATIENT BIRTHDATE]) as [PATIENT BIRTHDATE],

  Age(today(),[PATIENT_BIRTHDATE]) as [Patient Age],

    Class(Age(today(),[PATIENT_BIRTHDATE]),10,'Age Range') as [Patient Age Group],

jonathandienst
Partner - Champion III
Partner - Champion III

I think YY dates below a threshold are considered to be part of the 21st century (so 61 -> 2016, while YY dates below that are considered to be part of the 20th (95 -> 1995).

On my system, the YY years are considered to be in the range 1970 to 2069, so 61 is seen as 2061, and 95 is seen as 1995. This is a snip from the control panel:

Capture.PNG

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

The statement was an example. Without a lot more details about your exact requirements, data model and sample data, I can only guess, but the line example I provided needs to replace the [PATIENT_BIRTHDATE] field as there can only be single instance on the LOAD statement.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Ok , so what you suggest now?