Results 1 to 10 of 10

Thread: Date Formulas

  1. #1
    Join Date
    May 2008
    Posts
    14

    Unanswered: Date Formulas

    Hi,
    I have been asked to produce a report that has the following info based on dates;
    8 WEEK SALES
    26 WEEK SALES
    52 WEEK SALES

    Im ok with 4 week sales (MonthToDate) but whats 8 week sales?
    wouldnt have a clue what the formula for 26 week sales?
    Im ok with the 52 week sales (YearToDate)

    Any help would be most appreciated.

    Darren

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE  your_date BETWEEN @date And DateAdd(ww, -8, @date)
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    14
    Hi Georgev,
    Many thanks for you reply but not sure I understand the syntax.
    My code below results in me retrieving the last 4 weeks Units Transaction Qtys which works fine. How would I use your code to retrieve the last 8 weeks?


    Code:
    IF {Sales_Order_Detail_File.DateTransactionJulian} in LastFullMonth
    THEN
    {Sales_Order_Detail_File.UnitsTransactionQty}
    ELSE 0

    Thanks
    Darren

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change the LastFullMonth bit to be todays date and todays date minus 8 weeks.

    The DateAd[d] function should be in your helpfiles - the syntax may be different to what I've posted above.
    George
    Home | Blog

  5. #5
    Join Date
    May 2008
    Posts
    14
    Cheers, I'll give it a go.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you get stuck post back, equally if you solve your problem post the solution back here too so that future readers can benefit from your experience

    Good luck!
    George
    Home | Blog

  7. #7
    Join Date
    May 2008
    Posts
    14

    Thumbs up

    Certainly will do - Thanks for your help.
    Darren

  8. #8
    Join Date
    May 2008
    Posts
    14
    Still cant get the formula to work........
    I have posted my code below:


    Code:
    IF ({Sales_Order_Detail_File.DateTransactionJulian} Date(), -8
    THEN
    {Sales_Order_Detail_File.UnitsTransactionQty}
    ELSE 0
    I want to show the Last 8 weeks TransQty

    Thanks in advance

  9. #9
    Join Date
    May 2008
    Posts
    14
    Just wondering if anyone can my incorrect syntax is the following code:
    Code:
    WHERE  {Sales_Order_Detail_File.DateTransactionJulian} BETWEEN CurrentDate And DateAdd("ww",-8,CurrentDate)
    When I try to save or run I get an error message stating that "the remaining text does not appear to be part of the formula" All of the code becomes highlighted.

    Have I missed a bracket or two out?

  10. #10
    Join Date
    Oct 2005
    Posts
    178
    Try this..for 8 weeks at 7 days per week = 56
    {DateTrasnsaction} in CurrentDate - 56 to CurrentDate

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •