Custom Fiscal Year to Date Formula to be used on Saved Searches

LAST FISCAL YEAR TO DATE

CASE WHEN {datecreated} BETWEEN to_date('8/1/2010', 'MM/DD/YYYY') AND to_date(to_char({today},'YYYY')-1||to_char({today},'MMDD'),'YYYYMMDD') THEN 1 ELSE 0 END

 

 

THIS FISCAL YEAR TO DATE

CASE WHEN {datecreated} BETWEEN to_date('8/1/2011', 'MM/DD/YYYY') AND to_date({today}) THEN 1 ELSE 0 END

 

1) The first formula will return a value of 1 when the Date of Creation is between the first day of the last fiscal year and the date exactly a year ago.  Otherwise, it is zero.

 

2) The second formula will return a value of 1 when the Date of Creation is between the first day of the this fiscal year and the date exactly a year ago.  Otherwise, it is zero.

 

3) {datecreated} could be replaced with any other date fields.

 

4) 1 could be replaced with any other field that needs to be returned (eg. {amount}).

 

5) Sample fiscal year starts 8/1/2010.

 

6) The first formula will return a value of 1 when the Date of Creation is between the first day of the last fiscal year and the date exactly a year ago.  Otherwise, it is zero.