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

[#2]

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

You can check this new version?

[#3]

Can you check my last version?

[hopkinsc]

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)

[#5 · Accepted solution]

Hi hopkinsc,

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

Bye

[hopkinsc]

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

[#7]

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

[#8]

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

Change:

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

to

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

AND

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

to

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

to

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