Hi,
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.
Thanks,
Nick
