COGNOiSe.com - The IBM Cognos Community

Hi Team,

I want to implement the below logics in a prompt.
This will be cascaded using conditional block, but please help me in acheving the below requirements.

1.Year to Date (YTD)
2. Calender Year to Date (CTD): Current Date - 365 days
3. Rolling Year to Date: this would be prior month end date - prior month end date (less one year)

Quote from: rajeurokz on 01 Sep 2015 07:23:00 AM
Hi Team,

I want to implement the below logics in a prompt.
This will be cascaded using conditional block, but please help me in acheving the below requirements.

1.Year to Date (YTD)
2. Calender Year to Date (CTD): Current Date - 365 days
3. Rolling Year to Date: this would be prior month end date - prior month end date (less one year)

Are you using a dimensional package or a relational package? The solution is different for each.

MF.

Thanks for the reply, I'm using a relational package.

Any update on this query ????

Quote from: rajeurokz on 01 Sep 2015 07:23:00 AM
Hi Team,

I want to implement the below logics in a prompt.
This will be cascaded using conditional block, but please help me in acheving the below requirements.

1.Year to Date (YTD)
2. Calender Year to Date (CTD): Current Date - 365 days
3. Rolling Year to Date: this would be prior month end date - prior month end date (less one year)

Seems to me that YTD would be

[your date item] between _make_timestamp(extract(year, current_date),1,1) and current_date

CTD would be

[your date item] between _add_years (current_date,-1) and current_date

RYTD would be

[your date item] between _add_years(_last_of_month(_add_months(current_date,-1)),-1) and _last_of_month(_add_months(current_date,-1))

Cheers!

MF.

This should fit my requirement, i'll try and enhance this further.


Thanks !!