Solved

# Select Data for Last Fiscal Qty  and Current Fiscal Qtr

Posted on 2012-08-17
306 Views
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?
0
Question by:trilian

LVL 100

Expert Comment

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

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

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

mlmcc
0

Author Comment

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

LVL 100

Expert Comment

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

mlmcc
0

Author Comment

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
0

LVL 34

Expert Comment

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
0

Author Comment

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

LVL 34

Accepted Solution

There's probably a simpler/shorter way to do this, but this seems to work.

Create four formulas under Formula Fields in the Field Explorer, using the indicated formula names:

// start_date_TY
Select Month (CurrentDate)
Case 12 :
Date (Year (CurrentDate), 12, 1)
Case 1, 2 :
Date (Year (CurrentDate) - 1, 12, 1)
Case 3, 4, 5 :
Date (Year (CurrentDate), 3, 1)
Case 6, 7, 8 :
Date (Year (CurrentDate), 6, 1)
Case 9, 10, 11 :
Date (Year (CurrentDate), 9, 1)

// end_date_TY
Select Month (CurrentDate)
Case 12 :
Date (Year (CurrentDate) + 1, 3, 1) - 1
Case 1, 2 :
Date (Year (CurrentDate), 3, 1) - 1
Case 3, 4, 5 :
Date (Year (CurrentDate), 6, 1) - 1
Case 6, 7, 8 :
Date (Year (CurrentDate), 9, 1) - 1
Case 9, 10, 11 :
Date (Year (CurrentDate), 12, 1) - 1

// start_date_LY
Select Month (CurrentDate)
Case 12 :
Date (Year (CurrentDate) - 1, 12, 1)
Case 1, 2 :
Date (Year (CurrentDate) - 2, 12, 1)
Case 3, 4, 5 :
Date (Year (CurrentDate) - 1, 3, 1)
Case 6, 7, 8 :
Date (Year (CurrentDate) - 1, 6, 1)
Case 9, 10, 11 :
Date (Year (CurrentDate) - 1, 9, 1)

// end_date_LY
Select Month (CurrentDate)
Case 12 :
Date (Year (CurrentDate), 3, 1) - 1
Case 1, 2 :
Date (Year (CurrentDate) - 1, 3, 1) - 1
Case 3, 4, 5 :
Date (Year (CurrentDate) - 1, 6, 1) - 1
Case 6, 7, 8 :
Date (Year (CurrentDate) - 1, 9, 1) - 1
Case 9, 10, 11 :
Date (Year (CurrentDate) - 1, 12, 1) - 1

Then your record selection formula would be something like this:

{date field} in {@start_date_TY} to {@end_date_TY} or
{date field} in {@start_date_LY} to {@end_date_LY}

James
0

Author Closing Comment

Thank you very much!
0

LVL 34

Expert Comment

You're welcome.  Glad I could help.

James
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

#### 759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!