0 Replies Latest reply: Apr 29, 2010 10:18 AM by Nicholas Weston RSS

    Manipulating Week Numbers


      Im having trouble manipulating dates using the "Week" function in the script. I want to be able to reassign what date the "week" function works from.

      I want the week numbering to start at the last week of september for each year i.e. so Week 1 is 28/09/2009 and not 04/01/2010. I'm using the below coding but it doesn't take into consideration weeks with 53 weeks in them.

      LOAD Date,

      Week([Date]) as Week,

      if((week([Date])-39)<=0, (week([Date])-39)+52, (week([Date])-39)) as [Financial Week]

      Is there a way to count the number of weeks in any given year in the code so that I can replace the "+52" part? At the moment, the code is giving me two financial week 14's when you look at 28/12/2009 and 04/01/2010.

      Please look at the attached file to see what i mean.