?
Solved

Select Data for Last Fiscal Qty  and Current Fiscal Qtr

Posted on 2012-08-17
9
Medium Priority
?
309 Views
Last Modified: 2012-08-28
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
Comment
Question by:trilian
  • 4
  • 3
  • 2
9 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 38306459
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
0
 
LVL 1

Author Comment

by:trilian
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

by:mlmcc
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 1

Author Comment

by:trilian
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

by:James0628
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

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

Accepted Solution

by:
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

by:trilian
ID: 38341205
Thank you very much!
0
 
LVL 35

Expert Comment

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

 James
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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

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

Join & Ask a Question