Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: date range formula Post Reply Post New Topic
Author Message
STCPD
Newbie
Newbie
Avatar

Joined: 08 Jun 2012
Online Status: Offline
Posts: 18
Quote STCPD Replybullet Topic: date range formula
    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!
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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} >= {@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.
IP IP Logged
STCPD
Newbie
Newbie
Avatar

Joined: 08 Jun 2012
Online Status: Offline
Posts: 18
Quote STCPD Replybullet 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.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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} >= {@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.
IP IP Logged
STCPD
Newbie
Newbie
Avatar

Joined: 08 Jun 2012
Online Status: Offline
Posts: 18
Quote STCPD Replybullet 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
IP IP Logged
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.