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