formula for date range prior to date search parameter

[STCPD]

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.

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})

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.

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})

 

[IanWaterman]

I assume this is for your select statement, try. Where Date filed is your field to filter date range on

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

Ian

 

[STCPD]

Maybe I'm not doing something right but it didn't seem to work.
I used
{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"]
in a formula field and it came back with a true/false value. I'm looking for a numerical value.
Then I tried to use the formula to evaluate in a running total field but that didn't work either. It did come back with a numerical value but it was incorrect.

Any other sugguestions?

I appreciate your help!

 

[Charliy]

Use Ian's select formula, then set up two Running Totals.

For one the evaluate formula is: {datefield} in {?StartDate} to {?EndDate}

For the other use: {datefield} in dateadd("d", -datediff("d",{?StartDate}, {?EndDate}), {?StartDate}) to {?StartDate}-1

 

[STCPD]

I'm sorry I'm new to Crystals. I don't know what you mean by two running totals. Is this all going to go into the running total field I created?

 

[IanWaterman]

As Charliy said create two Running totals one for daterange entered and one for prior x days.

In the evaluate conditions formula enter the two formula Charliy suggested.

The formula I gave you is a boolean as its designed to reduce dataset brought back to report, restricting it to the records you require. If you did not do that you would bring back entire dataset and then only count the ones you wanted which would make report very slow to execute.

Ian

 

[STCPD]

Thanks for your help but apparently I'm going to need someone to spell it out for me. I've spent days trying to get this to work and I think I'm about to throw in the towel! I've only been working with Crystal the last month so I'm easily confused

 

[IanWaterman]

Show us the formulae and any conditions you are using for Running Totals. Impossible to spell out until we know what you have tried and what is going wrong.

Ian

 

[STCPD]

In the running total, I'm summarizing lwcharg.ucr_code as a count. In the evalute section I have tried these formulas.

{lwmain.date_rept} >= dateadd("d", -datediff("d",{?START DATE}, {?END DATE}), {?START DATE}) and {lwmain.date_rept} <= {?END DATE};
{lwchrg.ucr_code}="220" 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

 

[STCPD]

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

 

[IanWaterman]

Have you looked in the show SQL to ensure {lwmain.date_rept}>={@STARTFORMULA} is actually resolved.

If this date filter is not parsed to database then report will bring back all data and then filter locally. This will be OK with a small dataset but as it grows over time you may find report runs slower and slower.

You can just use

{lwmain.date_rept}>=DATEADD("D",-DATEDIFF("D",{?START DATE},{?END DATE}),{?START DATE})

Hopefully that will resolve and parse to database.

Ian