Some fiscal year help needed... 1

[stormtrooper]

Hi. I have a report that currently shows the records for year-to-date (CR7). But I really need fiscal-year-to-date. In otherwords, I need to select all records that fall within my fiscal year which always begins on April 1 and ends March 31.

I'm thinking it will probably look something like this:
{MyDate} in {@Some_Begin_Dt_Formula} to {@Some_End_Dt_Formula}

Thanks

 

[synapsevampire]

Looks about right, though I prefer the explicit:

(
{MyTable.MyDate} >= {@FiscalStartDate}
and
{MyTable.MyDate} <= {@FiscalEndDate}
)

Create 2 formulas, one to return each date, and make sure you do NOT use any variables in the formulas (they'll negate pass through SQL).

If your start date is July 1, then it will be something like:

@FiscalStartDate
if month(currentdate) < 6 then
date(year(currentdate)-1,7,1)
else
date(year(currentdate),7,1)

@FiscalEndDate
if month(currentdate) < 6 then
date(year(currentdate),6,30)
else
date(year(currentdate)+1,6,30)

Something like that...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com