Solved

# Select Data for Last Fiscal Qty  and Current Fiscal Qtr

Posted on 2012-08-17
Medium Priority
309 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
• 4
• 3
• 2

LVL 101

Expert Comment

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

or

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

mlmcc
0

LVL 1

Author Comment

ID: 38306517
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 101

Expert Comment

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

mlmcc
0

LVL 1

Author Comment

ID: 38306614
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 35

Expert Comment

ID: 38307341
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

LVL 1

Author Comment

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

LVL 35

Accepted Solution

James0628 earned 2000 total points
ID: 38315137
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

LVL 1

Author Closing Comment

ID: 38341205
Thank you very much!
0

LVL 35

Expert Comment

ID: 38341517
You're welcome.  Glad I could help.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
###### Suggested Courses
Course of the Month15 days, 3 hours left to enroll