Print Page | Close Window

date range formula

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=16709
Printed Date: 22 May 2024 at 1:52am


Topic: date range formula
Posted By: STCPD
Subject: date range formula
Date Posted: 08 Jun 2012 at 6:56am

I'm working on a formula to calculate the number of criminal offenses that occurred in the same amount of days as my date search parameter but prior to the start date. The date search parameter is always changing. The idea is to compare the number of offenses from the last time period whether its days, weeks or years. I’m writing these in a running total field in the use a formula evaluate section. I’m summarizing count  on lwchrg.ucr_code.

For instance say my date search parameter is 5/1/2012 to 5/31/2012.

I can get the number of offenses easily by using:

{lwchrg.ucr_code}="23F" AND {lwmain.zone} IN ["Z1","Z2","Z3","Z4","Z5","Z6","Z7","Z8"]

But I'm having trouble with the prior date portion.

This got me the number of days in the search parameter which in this example would be 30.

numberVar days :=DateDiff ("d",{?START DATE} , {?END DATE})

This got me the start date that I was looking for which in this example would be 4/1/2012:

DateAdd ("d",-numberVar days :=DateDiff ("d",{?START DATE} , {?END DATE}) ,{?START DATE} )

So I came up with this but it is not working. I get an error that says the formula cannot be a range.

DateAdd ("d",-numberVar days :=DateDiff ("d",{?START DATE} , {?END DATE}) ,{?START DATE} ) to dateadd ("d",-1,{?START DATE})

And I’ve tried this.

 {lwmain.date_rept} >= dateadd("d", -datediff("d",{?START DATE}, {?END DATE}), {?START DATE}) and {lwmain.date_rept} <= {?END DATE};
{lwchrg.ucr_code}="23F" AND {lwmain.zone} IN ["Z1","Z2","Z3","Z4","Z5","Z6","Z7","Z8"]

And

if {lwchrg.ucr_code}="220" AND {lwmain.zone} IN ["Z1","Z2","Z3","Z4","Z5","Z6","Z7","Z8"] then

select

{lwmain.date_rept}>=dateadd ("d",-datediff("d",{?START DATE},{?END DATE}),{?START DATE}) and {lwmain.date_rept}<={?END DATE}

Case

{lwmain.date_rept} in {?START DATE} to {?END DATE} and {lwmain.date_rept} in dateadd("d",-datediff("d",{?START DATE},{?END DATE}),{?START DATE}) to {?START DATE}-1

Now I’m completely lost and about ready to throw in the towel. Please help!


Replies:
Posted By: rkrowland
Date Posted: 10 Jun 2012 at 11:23pm
Are you selecting records based on the date parameters?
 
IE a user inputs 01-May-12 to 31-May-12 (I try to use this date format as US and Europeans read dates differently - ie in the UK the dates you used would be 05-Jan-2012 and 05-month31-2012) and the report only returns records between those dates?
 
If that's the case you'll first need to amend your selection filters to include the prior x days in the report as well - you can't perform analysis on data which isn't in the report.
 
This is how I'd do it.
 
@StartFormula
dateadd("d",-datediff({?start date},{?end date}),{?start date})
 
Like your formula above this will simply give a new "start date" for record selection to include the prior period in the report - apply your record selection as follows;
 
{lwmain.date_rept} >=  mailto:%7b@StartFormula - {@StartFormula }


and




{lwmain.date_rept} <= {?end date}
 
I would then create another formula as follows;
 
@period
if {lwmain.date_rept} < {?Start Date}


then "Prior Period (" & totext(datediff({?start date},{?end date}),0,"") & " days)"


else "Current Period"
 
You can then group on that formula to perform easy analysis on the two separate periods.
 
Regards,
Ryan.

Posted By: STCPD
Date Posted: 11 Jun 2012 at 4:46am
Yes I am selecting records based on date parameters. This is what I have in the record selection.
{lwmain.date_rept}IN {?START DATE} TO {?END DATE}
So I'm a little confused. I'm making a change to the above formula? If so I'm not sure how to add the formula you provided without getting errors.

Posted By: rkrowland
Date Posted: 11 Jun 2012 at 10:13pm
You need to create the @StartFormula first and then change your record selection criteria to the following;
 


{lwmain.date_rept} >=  mailto:%7b@StartFormula - {@StartFormula }


and




{lwmain.date_rept} <= {?end date}
 
You need to include the prior period in the record selection otherwise there is no way for you to perform any kind of analysis on it.
 
You can separate the 2 periods by creating the @period formula above and then grouping by it.
 
Regards,
Ryan.

Posted By: STCPD
Date Posted: 12 Jun 2012 at 5:16am
I figured it out! Thanks!
I changed my record selection to:
{lwmain.date_rept}>={@STARTFORMULA}
AND
{lwmain.date_rept}<={?END DATE}
 And I created a formula(@STARTFORMULA):
DATEADD("D",-DATEDIFF("D",{?START DATE},{?END DATE}),{?START DATE})
Then in my running total evaluate I used:
{lwchrg.ucr_code}="220" AND {lwmain.zone} IN ["Z1","Z2","Z3","Z4","Z5","Z6","Z7","Z8"] AND
{lwmain.date_rept} >= DATEADD("D",-DATEDIFF("D",{?START DATE},{?END DATE}),{?START DATE}) AND {lwmain.date_rept}<= {?START DATE} -1


Print Page | Close Window