topic Re: Help with Fiscal calendar in QlikView

Help with Fiscal calendar

hopkinsc — Tue, 07 Apr 2015 13:09:12 GMT

Hi all,

I have attached a sample qvw with a fiscal calendar script.

I have 2 issues that i am trying to get done..

i can't seem to get the Fiscal Year correct. the fiscal year starts on the 1st Monday of July but this calendar is changing the fiscal year on the 1st July every year.

e.g.

the 1st Monday in July in 2009 was the 6th July. but the fiscal year changes on the 1st. i want to see Fiscal Year of 2008/2009 up until 5th July 2009, then on 6th July it should change to 2009/2010.

is there an easy way i can get this single Fiscal Calendar used on 2 different dates in separate 'unlinked' tables.

if you look at the table structure, i have a delivery table which uses delivery date and a warehouse table which uses finished date. these 2 tables are not linked at all as they are completely unrelated data. is it best to have a separate calendar for each of these?

Thanks in advanced

Re: Help with Fiscal calendar

hopkinsc — Tue, 07 Apr 2015 14:27:15 GMT

Can anyone help with this please?

Re: Help with Fiscal calendar

malini_qlikview — Tue, 07 Apr 2015 15:32:49 GMT

Hi,

Please find the attached if it helps.

Re: Help with Fiscal calendar

hopkinsc — Tue, 07 Apr 2015 15:51:40 GMT

Hi, thanks for your reply.

This looks good but it seems that the 53 weeks in 2010 is knocking the rest of the years out by a week.

i.e. 2010 week 53 is changing the fiscal year up 1

2011 fiscal year is changing at week 52

is there a way around this?

Re: Help with Fiscal calendar

malini_qlikview — Tue, 07 Apr 2015 16:01:38 GMT

Hi,

Yeah this is because default Qlikview Week function is being used in the script. You need to redefine the week number based on your requirement. Please find a useful document for re defining Week Number which might be useful.

Re: Help with Fiscal calendar

Tue, 07 Apr 2015 16:20:53 GMT

Hi hopkinsc,

I made some changes to your script, in the calculation of the fiscal year, to solve the point 1.
Check the attached file

I simply changed the offset calculation function YearName that you used for FiscalYear

YearName(if(WeekDay(TempDate)='Mon' and Day(TempDate)<=7 and Month(TempDate)=7,TempDate,TempDate-WeekDay(TempDate)), 0 , $(vFiscalYearStartMonth)) AS FiscalYear

Regards

Re: Help with Fiscal calendar

hopkinsc — Tue, 07 Apr 2015 16:26:39 GMT

Thanks both for your replies, although i am still not understanding the week numbers. i know that some years have 53 weeks, in those years i do not want the fiscal year to roll on until the start of week 1.

Re: Help with Fiscal calendar

Tue, 07 Apr 2015 16:41:24 GMT

Use the following formula to calculate the Fiscal week:

Num(Right(WeekName(TempDate,0),2))-Week(Date(Year(TempDate)&'-06-30')) as [Fiscal Week]

Regards

Re: Help with Fiscal calendar

hopkinsc — Tue, 07 Apr 2015 18:14:34 GMT

Hi Thanks, but that didnt work. if i un select the year in your example i get 0 week numbers and also negatives.

will fixing the week numbers also fix the fiscal year where it is changing prematurely?

Re: Help with Fiscal calendar

Anonymous — Tue, 07 Apr 2015 20:38:15 GMT

Please see the attached file. The only problem with fiscal year calcluation that I see is,

It should start on First Monday of July every year and year end date should be same date - minus 1 day, next year.

In some cases there will be overlap when end date will first Monday of july of next year.

Re: Help with Fiscal calendar

Tue, 07 Apr 2015 21:33:21 GMT

Sorry, I did not consider the change of the year to 31/12

You can check this new version?

Re: Help with Fiscal calendar

Tue, 07 Apr 2015 21:34:45 GMT

Can you check my last version?

Re: Help with Fiscal calendar

hopkinsc — Wed, 08 Apr 2015 07:47:16 GMT

thanks for this, its the best so far, the only thing i can see thats wrong is 01/07/2013 is a Monday so therefore should be Week 1 of financial year 2013/2014 but it is showing as week 52 of financial year 2013/2014.

and also there is another week 52 of the same financial year which starts on 30/06/2014 (which is the correct one)

Re: Help with Fiscal calendar

Wed, 08 Apr 2015 08:20:03 GMT

Hi hopkinsc,

you can check this latest version, I should have corrected even the problem you reported.

Bye

Re: Help with Fiscal calendar

hopkinsc — Wed, 08 Apr 2015 11:14:20 GMT

thanks everyone for your help, the last answer from Voto Gennaro worked!

Re: Help with Fiscal calendar

Mon, 15 Feb 2016 07:26:57 GMT

‌hi Gennaro thanks for sharin, what needs to be changed if my fiscal year begin in November 1st?

Re: Help with Fiscal calendar

Mon, 15 Feb 2016 16:38:42 GMT

You can change the end date and the reference of the calculation of the fiscal week

Change:

LET EndDate = num(MakeDate(2020,6,30));

LET EndDate = num(MakeDate(2020,11,1));

AND

num(Right(WeekName(TempDate,-num(Week(Date(Year(TempDate)&'-11-01')),'#')),2)) as [Fiscal Week],

AND

YearName(if(WeekDay(TempDate)='Mon' and Day(TempDate)<=7 and Month(TempDate)=7,TempDate,TempDate-WeekDay(TempDate)), 0 , $(vFiscalYearStartMonth)) AS FiscalYear

YearName(if(WeekDay(TempDate)='Mon' and Day(TempDate)<=7 and Month(TempDate)=12,TempDate,TempDate-WeekDay(TempDate)), 0 , $(vFiscalYearStartMonth)) AS FiscalYear

I can not try it now, but it should be enough these changes

Regards

Gennaro