Count From The Current Fiscal Year Start Date
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21832
Printed Date: 29 Apr 2024 at 4:55am
Topic: Count From The Current Fiscal Year Start Date
Posted By: kitster100
Subject: Count From The Current Fiscal Year Start Date
Date Posted: 01 Jan 2016 at 5:23am
Hi:
I am trying to report on a count of cases for the current fiscal year (FY) to the last day of the previous month. Our FY runs from October 1, 2015.
Last year I created a report and as the start date, used:
if {database.Date} in date(year(currentdate),10,1) to currentdate - Day(CurrentDate) then 1 else 0
This worked in 2015. However, we're now in 2016, and it no longer works. It appears to interpret the above formula as:
From 10/1/2016 to 12/31/2015.
So I tried:
if {Epiccases.Date} in dateserial(2015,10,1) to currentdate - Day(CurrentDate) then 1 else 0
which works perfectly. However, the year 2015 is hardcoded, which is what I'm trying to avoid.
Ideally, the desired solution would be for the formula to automatically count from the most recently passed October 1 to the last day of the previous month (which, as of today is Dec 31, 2015), no matter what year it currently is. So after October 1, 2016, it should start using 10/1/2016 as the beginning date, and so on.
Is this possible? Thank you in advance.
|
Replies:
Posted By: DBlank
Date Posted: 04 Jan 2016 at 2:36am
so for all of october you want it to return 0?
{Epiccases.Date} in Date(year(currentdate)-(if month(currentdate)>=10 then 0 else 1),10,1) to dateserial(year(currentdate),month(currentdate),1-1)
|
Posted By: kitster100
Date Posted: 04 Jan 2016 at 3:33am
|
No. I want a count of all cases from October 1, 2015 to December 31, 2015. Then when I run it next month (February 2016) , I want a case count from October 1, 2015 to January 31, 2016. And I want the FROM criteria to reset each October to the most recent October. So when I run it in November 2016, it will capture ONLY cases from October 2016 forward.
|
Posted By: DBlank
Date Posted: 04 Jan 2016 at 3:36am
i understand and that is what I posted for you.
however what you requested seems to explicitly create the value of 0 when you run the report anytime in October of any year.
|
Posted By: kitster100
Date Posted: 04 Jan 2016 at 3:41am
|
Hmm, I don't want that. When I run it in October, then my desired results should be from the PREVIOUS year's October 1 to the last day of the previous month. So, for example, if I run the report on October 5 2016, then the desired result will be all cases from October 1, 2015 to September 30, 2016.
|
Posted By: DBlank
Date Posted: 04 Jan 2016 at 4:18am
|
{Epiccases.Date} in Date(year(currentdate)-(if month(currentdate)>=11 then 0 else 1),10,1) to dateserial(year(currentdate),month(currentdate),1-1)
|
Posted By: kitster100
Date Posted: 04 Jan 2016 at 5:10am
This gives me ALL the results in the database. Thanks much for your efforts. I will continue to play with it. In the meantime I'll just use the hardcoded formulas.
Thanks again.
|
Posted By: DBlank
Date Posted: 04 Jan 2016 at 5:40am
this is a select statement meant to limit the data set of the entire report, it is not a formula field.
did you place it in your select expert?
|
Posted By: kitster100
Date Posted: 05 Jan 2016 at 8:53am
No, because I have other formulas which require data from the last Fiscal Year (October '14 thru September '15).
My report is a crosstab with the following columns (the date range I'm trying to capture is in italics, in the parentheses):
Prev Month (December 2015) | Same Month Last Year (December 2014) | Current FY YTD (October '15- December '15) | Previous FY YTD (October '14 - December '14).
I have no parameters set and no conditions in the select statement. Instead, I have formulas for each of the result sets above to set the date criteria. My results depend on calculating from the most recent October (for current FY) and for the October before that (for the previous FY). Getting the formula to recognize the October start of our FY has been the problem. So far, all I've been able to come up with is hardcoding in the October 1 start date, as I mentioned in the original post.
Any ideas?
Thank you for your help.
|
Posted By: DBlank
Date Posted: 05 Jan 2016 at 9:15am
|
if {Epiccases.Date} in Date(year(currentdate)-(if month(currentdate)>=11 then 0 else 1),10,1) to dateserial(year(currentdate),month(currentdate),1-1) then 1 else 0
|
Posted By: kitster100
Date Posted: 05 Jan 2016 at 9:37am
This is terrific! It calculates this year's FY Year to Date (October '15 - December '15) perfectly.
Do you know a formula to get LAST Year's FY YTD (October '14 - December '14)?
Thank you.
|
Posted By: DBlank
Date Posted: 06 Jan 2016 at 4:13am
maybe this?
if {Epiccases.Date} in Date(year(currentdate)-(if month(currentdate)>=11 then 1 else 2),10,1) to dateserial(year(currentdate)-1,month(currentdate),1-1) then 1 else 0
|
Posted By: kitster100
Date Posted: 07 Jan 2016 at 7:38am
|
This works beautifully. Thanks for all your help!
|
|