Legacy Business Intelligence > COGNOS Impromptu

Letter to 18 year olds the following week


I would like some help filtering for customers turning 18 years old next week.

Do you have a column which specifies what birthdate ?

Birthdate in_range{20010902:20010908)

might not be the best solution thoug, since Days can vary..

My dob field shows as "Date of Birth (MAST)"

This will be part of my filter so that the report (letter) scheduled for each Monday at 7:00AM, will select and print individuals whose 18th birthday falls on one day that week.

You have to create a data item that iterates through all the birth dates and matches the ones that are 18 years old in the upcoming week. So you have to convert all the Birth days into birth weeks instead. For exempel if your birth day is 1985-09-02 then the birth week would be 1985-36. To accomplish this you have to extract year and week from Birth day and the same for current_date. After that you create a case statement to find matches, so when you have a match, it will return a 1 otherwise it will return a 0. Last of all you filter the report on [Birthdays next week] = 1.

cast(extract(year,[Birthdate]);varchar(4)) + '-' + cast(_week_of_year ([Birthdate]);varchar(2)) = cast(_add_years(current_date,-18);varchar(4)) + '-' + cast(cast(_week_of_year (current_date);integer)+1;varchar(2))
then 1
else 0


[0] Message Index

Go to full version