Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielle_v
Creator
Creator

Fiscal Weeks - Week 1 in September

Hi All,

I'm trying to create a calendar field in my script to change the week numbers so that week 1 starts on the first Monday in September.

So, 03/09/2012 is usually week 36, but I want it to be week 1.

Similarly, 31/12/2012 is the first day of week 1 in my existing calendar, but I would want this date to be in week 18 for my new week field.

I thought I had done what I needed to do by using the MakeDate function in my script. This seems to work perfectly up until 07/01/2013, when suddenly the week numbers on my new week field changed to be Sunday starting weeks (rather than Monday starting as they were up until that point).

Any help greatly appreciated.

Thanks,

Danielle

1 Solution

Accepted Solutions
Not applicable

Right(WeekName(TempDate,0,-36),2) as NewWeek

then your week 36 will start by 1 i think

View solution in original post

3 Replies
Not applicable

Right(WeekName(TempDate,0,-36),2) as NewWeek

then your week 36 will start by 1 i think

danielle_v
Creator
Creator
Author

Many thanks, this has achieved exactly what I need

Not applicable

Very useful discussion. I am leveraging this to create a fiscal calender that starts in october (starts on saturday for the week whose monday is first monday of October).

I am using below script to adjust the weeks. So fiscal week 1 in 2012 starts on Sat, 1st Oct 2011 and goes on.

right (WeekName(TempDate,13,-2),2) as FiscalWeek

if(Right(WeekName(TempDate,13,-2),2)>0 and Right(WeekName(TempDate,13,-2),2)<=13,YearName(TempDate,1),YearName(TempDate,0)) as FiscalYear,

Weeks are working perfect but I am facing problem in scripting years properly. I am incrementing year with the script above (logic: for first 13 quarters increment calender year by 1 to get fiscal year and after that use calender year as fiscal year). This does not apply to certain exceptions like 12/31/2012 (attached screenshot) as its in fiscal week 14 and calender year is still 2011 not 2012.

My question is how to handle years when starting fiscal calender different than calender year?

Thanks for helping with this. I very much appreciate it.

Capture.JPG