Help with Fiscal calendar

[hopkinsc]

Hi all,

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

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

1.

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.

2.

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

[hopkinsc]

Can anyone help with this please?

[malini_qlikview]

Hi,

Please find the attached if it helps.

[hopkinsc]

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?

[malini_qlikview]

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.

[#6]

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

[hopkinsc]

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.

[#8]

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

[hopkinsc]

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?

[Anonymous]

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.