Create a YTD Date Filter, No Prompt

[SarahCL]

Hi all,

I have been trying for a couple days now to try and find a solution to this.

I was asked to create a report that pulls data starting in October (YTD). I had initially created a filter that said From 1st October 2014 to current_date but ultimately I do not want to go in every year and change it to October 2015, 2016 etc.

I was looking at having the dates being dynamic but I am having a hard time with creating the Data items and the detail filter I need.

I need to have a date/data items/filter expression that says:

If current_date (which is the day the report is ran) is before 1st October of current year ran report from October 1st of last year to current_date, if not ran report from October 1st of current year to current_date.

Any help is appreciated! ;D

[cognos05]

is this dimensional reporting?
If so you can create an expression with a member unique name , which would automatically choose the appropriate date.

[BigChris]

[DateField] between _make_timestamp(If(month(current_date)>=10) then (year(current_date)) else (year(current_date)-1),10,1) and current_date

[SarahCL]

Sorry it looks like I never came back to say I found a solution. Here it is for others to use!

( extract( month, current_date ) in (10,11,12)
AND [Origin Date] BETWEEN _make_timestamp( extract( year,current_date ), 10, 1 )
AND current_date )
OR
( [Origin Date] BETWEEN _make_timestamp( extract( year,current_date )-1, 10, 1 )
AND current_date )

[bdbits]

Thanks for posting your solution. Too often people never do that and threads are left hanging.  :)