Previous Week formula not working for a weekend date

[jemsmom]

Using Crystal 2008 with SQL Server 2008 R2
I’m trying to count records into weekly buckets for the previous 4 weeks. Unfortunatly I only have one test record that falls on a weekend and it is not falling into the correct bucket.

Formula works properly to find Week 3 Start Date
//start date of week three
DateAdd('ww',-1,minimum(LastFullWeek))

Formula works properly to find Week 3 End Date
//End Date of Week Three
DateAdd('ww',-1,maximum(LastFullWeek))

Week 3 formula does not return a 1 for a record date of 10/5/2013
If {Command.CONTACT_DATE} in DateAdd('ww',-1,minimum(LastFullWeek))
to
DateAdd('ww',-1,maximum(LastFullWeek)) then 1 Else 0

Can anyone tell me what I am doing wrong?

Thank you for your time.

 

[pmax9999]

The LastFullWeek function returns a date range from Sunday to Saturday, so if run today would return Sunday, Oct 6 to Saturday Oct 12. A transaction on October 5 would have been in the previous week. If you want Saturday Oct 5 included you need the week to be from Saturday to Friday. You could amend the formula to offset the additional day by subtracting 1, eg:

[Code StartOfWeek]
Minimum(LastFullWeek) -1
[/Code]

[Code EndOfWeek]
Maximum(LastFullWeek) -1
[/Code]

Does this help?

Cheers
Pete

 

[jemsmom]

I apologize I was not very clear. My report looks like this:
Week 1 Week 2 Week 3 Week 4
Start Date 9/15/2013 9/22/2013 9/29/2013 10/6/2013
End Date 9/21/2013 9/28/2013 10/05/2013 10/13/2013
Record 1 10/01/2013 0 0 1 0
Record 2 10/07/2013 0 0 0 1
Record 3 10/05/2013 0 0 0 0
Record 4 9/22/2013 0 1 0 0

Week 3 Start Date
//Formula returns 9/29/2013
DateAdd('ww',-1,minimum(LastFullWeek))

Week 3 End Date
//Formula returns 10/5/2013
DateAdd('ww',-1,maximum(LastFullWeek))

Week 3
//returns 0 for a record date of 10/5/2013 works
If {Command.CONTACT_DATE} in DateAdd('ww',-1,minimum(LastFullWeek))
to
DateAdd('ww',-1,maximum(LastFullWeek)) then 1 Else 0

I've verified about 300 records and everyone works except this one record which happens to be the only 10/5/ record and also happens to be the only Saturday record I have in the database. I can't figure out why this wont work.

Thanks again for your time.
.

 

[pmax9999]

On that basis I am guessing that {Command.CONTACT_DATE} is a Date/Time, and where the time component puts it beyond 12:00 am on Dec 5.

I would therefore amend your formula to take the time out of the {Command.CONTACT_DATE} field as follows:

If 	Date({Command.CONTACT_DATE}) in	DateAdd('ww',-1,minimum(LastFullWeek)) 
 					to
					DateAdd('ww',-1,maximum(LastFullWeek)) 
Then 	1 
Else 	0

Hoe this helps.

Cheers
Pete

 

[jemsmom]

That worked! Thank you for your time Pete.