topic Re: exclusion criteria (age) in QlikView

exclusion criteria (age)

Mon, 22 Oct 2012 08:42:12 GMT

Ive got a problem with age selection and i hope that someone here can help me with it.

In my datawarehouse ive got the attribute BirthDateTTime (format: Aug 30 1965 12:00AM)

now i want to exclude people younger than 7 years old

can someone tell me how i can make this expression?

Re: exclusion criteria (age)

deepakk — Mon, 22 Oct 2012 08:50:26 GMT

hi,

First Calculate the age at script level

if(round(num(today()) - num(Date#( Aug 30 1965 12:00AM ,'MMM DD YYYY hh:mm))) <=7,'Y','N') as AGE_IND

And use this field in your expression as

Sum({<AGE_IND={"Y"}>} Amount)

Deepak

Re: exclusion criteria (age)

Anonymous — Mon, 22 Oct 2012 08:52:39 GMT

Load

BirthDateTTime

from xyz

where BirthDateTTime < addmonths(Today(), -84);

Something like this perhaps, or if you want to create a flag,

load

if(BirthDateTTime<addmonths(Today(), -84), 1, 0) as OlderThan7YearsFlag

from xyz..

Or you can do it in a set analysis expression in the front end.

It all depends on how/where you want to use the filtering.

Re: exclusion criteria (age)

Mon, 22 Oct 2012 09:16:50 GMT

Both solutions won't work.

the today() format is 10/22/2012

and the BirthDateTTime is Aug 30 1965 12:00AM.
Could this be the problem?

Re: exclusion criteria (age)

deepakk — Mon, 22 Oct 2012 09:18:55 GMT

hi,

Thats the reason y I have used the num function to convert both the fields to numeric.

Deepak

Re: exclusion criteria (age)

Anonymous — Mon, 22 Oct 2012 09:30:56 GMT

Try using Timestamp#() to interpret the long string value as a timestamp. Date#() might disregard the time part of the value.

Re: exclusion criteria (age)

MayilVahanan — Mon, 22 Oct 2012 10:19:45 GMT

Try in the textbox,whether the timestamp#() function gives the value or not? then if it gives, means check with the above expression, from that you can identify it..

hope it helps

Re: exclusion criteria (age)

Mon, 22 Oct 2012 10:21:32 GMT

HI benn,

Can you upload a sample QVW file

Re: exclusion criteria (age)

deepakk — Mon, 22 Oct 2012 11:59:14 GMT

hi,

Since its chr(50) , qlikview might be considering as text and hence you might have to convert into datetime.

YOu might have to break the text using subfield function or use the left , right function to get the result.

And use the makedate function to convert into date.

eg:

SubFiled(Aug 30 1965 12:00AM,' ',3) This will get you 1965 i.e Year

SubFiled(Aug 30 1965 12:00AM,' ',1) This will get you Aug further convert into number num(date#('AUG','MMM')) i.e 8

SubFiled(Aug 30 1965 12:00AM,' ',2) This will get you 30.

Now use the makedate function like below

num(Makedate(1965,8,30)

I hope you got my point.

Deepak

Re: exclusion criteria (age)

Mon, 22 Oct 2012 13:31:08 GMT

Ive changed the char(50) to datetime now.but i don't really get the Subfieled part.

Re: exclusion criteria (age)

Mon, 22 Oct 2012 14:48:37 GMT

qvw file is uploaded

Re: exclusion criteria (age)

MayilVahanan — Mon, 22 Oct 2012 15:32:30 GMT

Please check the attached file..

use timestamp() instead of timestamp#()

Hope that helps

Re: exclusion criteria (age)

Tue, 23 Oct 2012 11:06:38 GMT

Thank you for helping out, but have you seen that it still doesnt give the right selection? the result for a birthdate in 2012 is the same as the result for 1890 for example.

Re: exclusion criteria (age)

Tue, 23 Oct 2012 11:16:20 GMT

Change the expression into this:

if (round(num(today()) - num(Timestamp(CUSTOMER_BirthDTTM))) <=(7*365),'Y','N')

Re: exclusion criteria (age)

Tue, 23 Oct 2012 11:31:53 GMT

you can also use the age function try the expression:

if(age(BirthDateTTime) < 7, .....)

Re: exclusion criteria (age)

Tue, 23 Oct 2012 11:36:38 GMT

hey i forgot smth in the age function. he is the correction:

if(age(today(),BirthDateTTime) < 7, ....)

Re: exclusion criteria (age)

Tue, 23 Oct 2012 12:10:13 GMT

The solution.

Variable: age (today(),BirthDateTTime) (AS Age_exp)

Expr: if($(AGE_exp) <=7 ,1,0)

Re: exclusion criteria (age)

Tue, 23 Oct 2012 12:10:46 GMT

Thanks! i had to make a variable first though before it started working.