Discussion Board for collaboration related to Creating Analytics for QlikView.
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?
Go to Solution.
Variable: age (today(),BirthDateTTime) (AS Age_exp)
Expr: if($(AGE_exp) <=7 ,1,0)
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
where BirthDateTTime < addmonths(Today(), -84);
Something like this perhaps, or if you want to create a flag,
if(BirthDateTTime<addmonths(Today(), -84), 1, 0) as OlderThan7YearsFlag
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.
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?
Thats the reason y I have used the num function to convert both the fields to numeric.
Try using Timestamp#() to interpret the long string value as a timestamp. Date#() might disregard the time part of the value.
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
Can you upload a sample QVW file
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.
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
I hope you got my point.
Ive changed the char(50) to datetime now.but i don't really get the Subfieled part.