Crystal Syntax for finding Last Week's (Mon-Sun) data

Hi,

I'm trying to figure out the Crystal Syntax to bring back all data for last week (Mon - Sun) no matter what the current day is, and without using parameter fields.

Thank you in advance,

Parachute  

// This is the code I currently use with date parameters, but I want to do away the paramter field
// so I can schedule the report to run any day of the week and still bring back data from last 
// Mon-Sun
 
(({Ticket.OpenDate} in datetime({?BeginDate}) to datetime({?EndDate})+1)
or 
({@ResolveDate} in datetime({?BeginDate}) to datetime({?EndDate})+1)
or
({@ResolveDate} > datetime({?Begindate}) and {Ticket.OpenDate} < datetime({?EndDate})+1))

Open in new window

parachute_505Asked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
The problem is probably you have a DATE-Time field and the time on the LastFullWeek will be 00:00

You could use
dateadd("d",2,maximum(LastFullWeek)



(Date(({Ticket.OpenDate}) in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
(Date({@ResolveDate}) in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
(Date({@ResolveDate}) > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek))))


mlmcc
0
 
mlmccCommented:
Crystal has a LastFullWeek range function.  Unfortunately it returns Sun thru Sat

Try it this way

{Ticket.OpenDate}-1 in LastFullWeek

If you need the other date checks
(({Ticket.OpenDate} - 1 in LastFullWeek)
or
({@ResolveDate} in LastFullWeek)
or
({@ResolveDate} > Minimum(LastFullWeek) and {Ticket.OpenDate} < Maximum(LastFullWeek)+1))

mlmcc
 
0
 
parachute_505Author Commented:
mlmcc,

Thank you for the quick response.  I am new to using Crystal Syntax, could you explain the need to have "Maximum(LastFullWeek)+1)?  Why does the "+1" need to be there if maximum(LastFullWeek) alread refers to Sunday?

Thanks again

Parachute
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
mlmccCommented:
Maximum(LastFullWeek) is last Saturday.  SInce you want through sunday you have to add 1 day (actually 2 to get to Monday).

mlmcc
0
 
parachute_505Author Commented:
Perhaps I'm misunderstanding the LastFullWeek function.  My understanding is that the LasFullWeek() is Sunday-Saturday.  So I thought maybe if I wrote --> {Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)) that I would get Monday-Sunday

====================================================
This is what I came up with, but it is returning Monday-Saturday
====================================================
(({Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
({@ResolveDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)))
or
({@ResolveDate} > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek))))

====================================================
I need Monday-Sunday so I added "+1"
====================================================
(({Ticket.OpenDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)+1))
or
({@ResolveDate} in dateadd("d",1,minimum(LastFullWeek)) to dateadd("d",1,maximum(LastFullWeek)+1))
or
({@ResolveDate} > dateadd("d",1,minimum(LastFullWeek)) and {Ticket.OpenDate} < dateadd("d",1,maximum(LastFullWeek)+1)))
0
 
parachute_505Author Commented:
mlmcc,

Thanks again for your help.  I was able to use the syntac below and have it work 99.9% successfully.  The other 0.1% I used your suggestion on a field that uses the SUM function --- CDate(dateadd("d",2,maximum(LastFullWeek))) --- or ---  CDate(dateadd("d",1,maximum(LastFullWeek)+1))

// Syntax to return data for Previous Week (Mon-Sun) without using data parameter fields
(({Ticket.OpenDate} in CDate(dateadd("d",1,minimum(LastFullWeek))) to CDate(dateadd("d",1,maximum(LastFullWeek))))
or
({@ResolveDate} in CDate(dateadd("d",1,minimum(LastFullWeek))) to CDate(dateadd("d",1,maximum(LastFullWeek))))
or
({@ResolveDate} > CDate(dateadd("d",1,minimum(LastFullWeek))) and {Ticket.OpenDate} < CDate(dateadd("d",1,maximum(LastFullWeek)))))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.