Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
danielle_v
Contributor

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

Re: Fiscal Weeks - Week 1 in September

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

then your week 36 will start by 1 i think

3 Replies
Not applicable

Re: Fiscal Weeks - Week 1 in September

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

then your week 36 will start by 1 i think

danielle_v
Contributor

Re: Fiscal Weeks - Week 1 in September

Many thanks, this has achieved exactly what I need

Not applicable

Re: Fiscal Weeks - Week 1 in September

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

Community Browser