Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Count From The Current Fiscal Year Start Date Post Reply Post New Topic
Page  of 2 Next >>
Author Message
kitster100
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 18
Quote kitster100 Replybullet Topic: Count From The Current Fiscal Year Start 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.

Edited by kitster100 - 01 Jan 2016 at 5:51am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)

IP IP Logged
kitster100
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 18
Quote kitster100 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
kitster100
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 18
Quote kitster100 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
kitster100
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 18
Quote kitster100 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
kitster100
Newbie
Newbie


Joined: 19 Jul 2011
Online Status: Offline
Posts: 18
Quote kitster100 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.