Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
gupta-anki
New Contributor III

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
MVP
MVP

Re: Converting Age providing negative counts

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
gupta-anki
New Contributor III

Re: Converting Age providing negative counts

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

MVP
MVP

Re: Converting Age providing negative counts

See edit above

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Converting Age providing negative counts

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

gupta-anki
New Contributor III

Re: Converting Age providing negative counts

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],

MVP
MVP

Re: Converting Age providing negative counts

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
MVP
MVP

Re: Converting Age providing negative counts

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
gupta-anki
New Contributor III

Re: Converting Age providing negative counts

Ok , so what you suggest now?

Community Browser