Hi,
I have a report where I need to calculate dates between last week and the next three weeks (not counting current week). I'm familar with the first part, it could be done by using the LastFullweek function but not sure how I can avoid the current week and calculate the next three weeks.
Any help is greatly appreciated.
Thanks!
@archett13
Try this. I verified that it passes the dates to the database for processing and correctly returns the dates for the last full week or the next three weeks (sub your own date field for {Orders.Order Date}):
(
{Orders.Order Date} = LastFullWeek
or {Orders.Order Date} in (Minimum(LastFullWeek) +14) to (Maximum(LastFullWeek) + 28)
)
Here's the query from Database|Show SQL Query:
WHERE ((`Orders`.`Order Date`>={ts '2010-06-27 00:00:00'} AND `Orders`.`Order Date`<{ts '2010-07-04 00:00:00'}) OR (`Orders`.`Order Date`>={ts '2010-07-11 00:00:00'} AND `Orders`.`Order Date`<{ts '2010-08-01 00:00:00'}))
@IanWaterman
I tired that formula against the Xtreme sample database, but there were a few problems with it. Here's what I needed to do to get it to work:
(
{Orders.Order Date} = Lastfullweek or
(
{Orders.Order Date} > maximum(lastfullweek)+7 and
//changed "w" (weekday) to "ww" (weeks)
//added +1 to avoid dropping the last day of the next date period
{Orders.Order Date} = dateadd("ww", 4, (maximum(lastfullweek)))+1
//added right parentheses
)
)
Add a comment