How to deduce fiscal year in custom calender?

[#1]

I amtrying to create a fiscal calender that starts in October (starts on Saturday for the week whose Monday is first the Monday of October). So fiscal week 1 for 2012 starts actually on Sat, 1st Oct 2011 and goes on.

I am using below script to adjust the weeks and years.

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. However, I am facing problem in scripting years properly. To deduce fiscal year, I am incrementing calender year by 1 for first 13 fiscal weeks to deduce fiscal year and after that use calender year as fiscal year (Screenshot attached). This does not apply to certain exceptions like 12/31/2012 (attached screenshot and qvd) as its in fiscal week 14 and calender year is still 2011 not 2012.

My question is how to handle years when fiscal calender different than calender year? Attached is the qvd for your reference. In my login please look for dates like 12/31/2011, 12/20/2012/12/31/2012 to get to the problem.

Thanks for helping with this. I very much appreciate it.

[hic · Accepted solution]

If you use a variable to define the beginning of the fiscal year, the formulae usually become easier to handle. In my blog post Fiscal Year, I have an offset in months. You can most likely modify these formulae for your situation.

HIC

[#3]

Many thanks Henric. This is it!