Former Member

Calculate Dates for Last week and next three weeks

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!

10|10000 characters needed characters exceeded

Related questions

Former Member
Posted on Jul 08, 2010 at 03:11 PM

@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

)

)

10|10000 characters needed characters exceeded
• Former Member Former Member

Thank you all for you answers..

I tried Ianwaterman's solution as well as the modification of that version by Kurt Reinhardt but there seems to be a problem in retrieving the next three weeks data.

Kurt's solution:

(

{@Appointment Date} = LastFullWeek

or {@Appointment Date} in (Minimum(LastFullWeek) +14) to (Maximum(LastFullWeek) + 28)

)

The above formula worked great, got the results I was looking for. Since I never used the Dateadd function, I'll try to see if I can play around and make that one work as well.

Thanks again

Edited by: achett13 on Jul 8, 2010 6:47 PM

• Former Member
Posted on Jul 08, 2010 at 02:48 PM

Try

(

Datefield = Lastfullweek or

(Datefield >maximum(lastfullweek)+7 and

)

Outermost breackets allow you to add to other clauses in select expert.

Ian