Calculate only the last 3 FULL Weeks using crystal reports formula 1

[angel7170]

Hello,

How can I filter a report to just show only the last 3 FULL WEEK (not the current week)( SUNDAY - SATURDAY) records?

I am using the formula

datefield >= dateadd("WW", -3, today) but this includes the data from the current week too because I have startdate = today
How can I change the startdate = start from last Saturday.
Please assist. Thank you so much

 

[IanWaterman]

Try

Datefield <= dateadd("d",-(dayofweek(datefield)+1), datefield) and
Datefield >= dateadd("d",-(dayofweek(datefield)+22), datefield)

Ian

 

[angel7170]

Thank you so much for replying

I tried this formula it doesn't return anything. please assist.
Thank you

 

[IanWaterman]

Place each part in a separate formula and see if correct dates are returned

Ian

 

[GJParker]

{MyTable.DateField} >= Today - (DayOfWeek (Today,crSunday) - 1) - 22 and
{MyTable.DateField} <= Today - (DayOfWeek (Today,crSunday) - 1)

Gary Parker
MIS Data Analyst
Manchester, England

 

[IanWaterman]

OOPS

Well spotted Gary

Mine should have been

Datefield <= dateadd("d",-(dayofweek(currentdate)+1), currentdate) and
Datefield >= dateadd("d",-(dayofweek(currentdate)+22), currentdate)

Doh!

 

[angel7170]

Thank you both Gary and Ian!

I used the formula as you said,

{HPD_Help_Desk.Reported_Date} >= Today - (DayOfWeek (Today,crSunday) - 1) - 22 and
{HPD_Help_Desk.Reported_Date} <= Today - (DayOfWeek (Today,crSunday) - 1)

As of today , 3/18/2009
(Thursday) the output I got is starting from 2/21/2009(Saturday) to 3/15/2009 (Sunday)

But I want the data starting from 2/22/2009 (Sunday) to 3/14/2009(Saturday).
please assist!

Thank you

 

[angel7170]

I tried the below formula

{HPD_Help_Desk.Reported_Date} > Today - (DayOfWeek (Today,crSunday) - 1) - 22 and
{HPD_Help_Desk.Reported_Date} < Today - (DayOfWeek (Today,crSunday) - 1)

It works now. Thank you again