topic Fiscal Weeks (Week Numbers) in QlikView

Fiscal Weeks (Week Numbers)

stuwannop — Wed, 05 Dec 2012 09:25:05 GMT

HI Everyone

I'm having a bit of trouble with a calendar script. I want to create a field called Fiscal Week whereby the 1st April is always week one every year, instead of the 1st January being week 1. I've managed to do FiscalYear ok by using a variable for the month in which the Fiscal Year starts (4). I want to do the same for the weeks but I'm a bit stuck.

I initially thought I could use 13 as the variable, thinking the 1st April was always week but it's not. Any help would be greatly appreciated. I may be approaching this the wrong way.

Thanks

Stu

Re: Fiscal Weeks (Week Numbers)

daveamz — Wed, 05 Dec 2012 10:04:05 GMT

Hello Stu,

See the example attached.

Best Regards,

David

Re: Fiscal Weeks (Week Numbers)

stuwannop — Wed, 05 Dec 2012 10:49:04 GMT

Hi David

I've adapted the logic in that example to my existing calendar script and it works a treat. Thank you very much for the quick response really appreciate it.

Stu

Re: Fiscal Weeks (Week Numbers)

stuwannop — Wed, 05 Dec 2012 11:26:54 GMT

David

Sorry can I re-open this, I've just checked some of my data (and the attached example) some of the fiscal week values are showing negative numbers. For example, the 1st January is showing a value of -11 when really it should be 41.

I think the fiscal week formula will need tweaking but not sure how.

Thanks

Stu

Re: Fiscal Weeks (Week Numbers)

daveamz — Wed, 05 Dec 2012 12:51:42 GMT

Ok, I've tried a different approach and it seems to work but I can't figure out why QV take Sunday as first day of the week. I guess is something with OS's regional settings.

Regards,

David

Re: Fiscal Weeks (Week Numbers)

stuwannop — Wed, 05 Dec 2012 14:10:23 GMT

Thanks David looking good. I am trying to adapt it into my own calendar script and can't get it to work (your example works great - thank you). The difference is my data runs from 1st April 2006 and I want to measure the week numbers from there so I am getting different results to your example (I am still getting the negative numbers). The relevent bits of my script are:

LETFYearStart = 4;

LET LoadYears = 8;

LET StartDate = '01/04/2006';

LET varMinDate = Num(MakeDate(2006,4,1)); //Added a VARMinDATE to Calendar - don't think I need this now.

LET varFiscal = Num(MakeDate(2006,4,1))-Num(MakeDate(2006,1,1))-1; //day difference from Normal Year and Fiscal Year

LOAD

(RowNo() + Num(Date#('$(StartDate)','DD/MM/YYYY')) - 1) AS Date

AUTOGENERATE(($(LoadYears) * 365) + Floor(($(LoadYears) + 1) / 4));

STORE $(vRawDataTable) INTO ..\DBQVD\$(vRawDataTable).qvd;

DROP TABLE $(vRawDataTable);

END IF

LOAD

Text(Date(Date, 'YYYYMMDD')) AS [$(vTablePrefix) Date ID],

Date(Date, 'DD/MM/YYYY') AS [$(vTablePrefix) Date],

Year(Date) AS [$(vTablePrefix) Year],

Year(Date) & '-' & Month(Date) AS [$(vTablePrefix) Year-Month],

Month(Date) AS [$(vTablePrefix) Month],

Day(Date) AS [$(vTablePrefix) Day Of Month],

Week(Date) AS [$(vTablePrefix) Week],

Week (Date-$(varFiscal)) AS [$(vTablePrefix) FiscalWeek],

Weekday(Date) AS [$(vTablePrefix) Week Day],

'Q' & Ceil((Month(Date) / 3), 1) AS [$(vTablePrefix) Quarter],

etc etc.

Thanks for your patience.

Re: Fiscal Weeks (Week Numbers)

stuwannop — Wed, 05 Dec 2012 14:46:06 GMT

David

I got it working. Cut a long story short I was dropping the table I wanted and looking at the old one in my script. Works great.

Thanks so much for your help you've been a star.

Stu

Re: Fiscal Weeks (Week Numbers)

daveamz — Thu, 06 Dec 2012 11:09:11 GMT

Hello Stu,

Please check the previous file to be sure that week starts on Sunday, because I've checked it and there are some disparities (see 1/31/2009 where week #45 starts on Saturday).

I've made a 3rd version.

David

Re: Fiscal Weeks (Week Numbers)

Anonymous — Fri, 10 May 2013 10:38:34 GMT

Hi daveamz01,

Thanks for posting the logic here and it works like a charm. But I'm getting one very small strange value, i.e. 52 as the very first value in the 'Fiscal Week' column. Please can you suggest how it can be corrected as I tried to change to value from 90 to 89 in the script for 'Fiscal Week' but couldn't get the right result.

Re: Fiscal Weeks (Week Numbers)

daveamz — Fri, 10 May 2013 15:18:21 GMT

Hi,

See the attached file.

First day of the fiscal week is Monday.

Regards,

David