asked on

Select Data for Last Fiscal Qty and Current Fiscal Qtr

Our fiscal year is Dec 1 to Nov 30. I need to report and compare data only from Last Yr Fiscal Qtr to Current Year Fiscal Qtr. The report will be scheduled to run automatically.

How would i select data based on a date field for this time period?

Mike McCracken

If you run it on the first of the FY quarter you can use

{DateField} IN DateAdd('m',-3,CurrentDate) to CurrentDate
or
{DateField} IN DateAdd('m',-15,CurrentDate) to DateAdd('m',-12,CurrentDate)

If you want to be able to run it at any time then the formula becomes more difficult.

mlmcc

trilian

ASKER

That is one of the requests. They want the report to run every week with Current Fiscal Qtr information compared to LYFQ to date.

Mike McCracken

WHen you say current do you mean run today returns Jun-Aug 2012
Run on 1 Sep returns Sep-Nov 2012

mlmcc

trilian

ASKER

If ran today it will report Jun-aug 11 and Jun-aug 12. Ran during sept to nov it would report sept to nov 11 and sept to nov 12 and so on...
Thanks

James0628

Is your date field just a date, or does it include a time? If it includes a time, is the time set, or is it always 12 AM? It will make a difference in the formula.

James

trilian

ASKER

The field is a datetime field and always set at 12AM.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trilian

ASKER

Thank you very much!

James0628

You're welcome. Glad I could help.

James