Date Calculation Query

[nimishabrahamc]

Hi,

I am quite new to cognos. I wanted to add an expression definition which contains IF statement of 2 different fields and return a calculated field.

For eg:

if ([A] = '5' and [BDATE] != NULL) then ([BDATE] +  1 Year)
elseif
if ([A] = '4' and [BDATE] != NULL) then ([BDATE] +  2 Year)
else ('Unidentified')

i.e: IF A has the value 5 and BDATE IS NOT NULL Then return BDATE + 1 year else IF A has the value 4 and BDATE IS NOT NULL Then return BDATE + 2 year else return Unidentified

Also should I change the format of BDate? Right now it is 'Nov 6, 2014 12:00:00 AM'. If so how do I modify that.

Kindly suggest me the proper syntax for this.

Thanks.
acn

[bdbits]

I think you want something like this.

if([A]='5' and [BDATE] is not null)
then (cast(_add_years([BDATE],1),varchar(20)))
else if([A]='4' and [BDATE] is not null)
then (cast(_add_years([BDATE],2),varchar(20)))
else ('Unidentified')

The cast is necessary to ensure a consistent datatype is returned.

Alternatively, you could use case statements.

Formatting a data item on a report is as easy as right-click on the item, choose Style > Data format and pick from your myriad options. "Should" is your call.

I highly recommend you at least look through the Report Studio user guide (menu item Help > Contents). It covers the basics and then some. Or get some training.

[nimishabrahamc]

Solved. Thanks a mill!