2 Replies Latest reply: Aug 8, 2013 1:52 PM by Siddhatrth Choudhary RSS

    How to deduce fiscal year in custom calender?

      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.

       

      Capture.JPG