Skip to Content
author's profile photo Former Member
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!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    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

    //added right parentheses

    )

    )

    Add a comment
    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

  • author's profile photo Former Member
    Former Member
    Posted on Jul 08, 2010 at 02:48 PM

    Try

    (

    Datefield = Lastfullweek or

    (Datefield >maximum(lastfullweek)+7 and

    Datefield < dateadd("w", 4, (maximum(lastfullweek)))

    )

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

    Ian

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.