1 Reply Latest reply on Aug 14, 2012 8:23 AM by Mark Holtz

    weeknumber start at 1

    Andrew Milner

      I have changed the work week to start on a Monday using DATETRUNC('week',[Order Date]-1)+1 but now when I look at the Weeknumber for these new dates the first week of the year can in some cases start at week 2. How can I overcome this?

        • 1. Re: weeknumber start at 1
          Mark Holtz

          Could you post an example workbook?

           

          I'm guessing you would need to create your own calculated field to give you an offset week number, rather than relying on the standard native functionality.

           

          I think you'll want some sort of CASE or IF statement:

           

          IF DATEPART('day',[OrderDate]) = 1

          THEN DATEPART('week',[OrderDate])-1

          ELSE DATEPART('week',[OrderDate])

          END