0 Replies Latest reply: Aug 11, 2017 4:22 AM by Stephane KOSIOR RSS

    How to change the Fiscal Year ?

    Stephane KOSIOR

      Hi,

       

      I found the script as described below which creates a fiscal year calendar. The fiscal year starts on September and ends on August the 31st of each year. e.g : from 09/01/2016 to 08/31/2017, the fiscal year is called FY17.

       

      Having said that, from September 1st, 2017 :

        - from 09/01/2017 to 12/31/2017, the fiscal year must be called FY17B

        - for the next years, the fiscal year starts on January and ends on December 31st

      1. e.g : the fiscal year FY18 starts on January 1st, 2018 and ands on Decemebr 31st, 2018.

       

      How to adapt/modify the script below please ?

       

      Many thanks,

      Stephane

       

       

      SET vFiscalYearStartMonthSales = 9;

      LET vStartDateSales = Num(YearStart(Today(), -6));

      LET vEndDateSales = Num(YearEnd(Today(),1));

       

       

       

      FiscalCalendarSales:

       

      LOAD

      *,

      Dual('Q' & Ceil(FiscalMonthSales/3), Ceil(FiscalMonthSales/3)) AS FiscalQuarterSales, // Fiscal Calendar Quarter

      Dual(Text(Date(MonthEnd(Date_Sales), 'MMM')), FiscalMonthSales) AS FiscalMonthNameSales; // Fiscal Calendar Month Name

      LOAD

      *,

      Year(Date_Sales) AS YearSales, // Standard Calendar Year

      Month(Date_Sales) AS MonthSales, // Standard Calendar Month

      Date(MonthEnd(Date_Sales), 'MMM') AS MonthNameSales,  // Standard Calendar Month Name

      Dual('Q' & Ceil(Month(Date_Sales)/3), Ceil(Month(Date_Sales)/3)) AS QuarterSales,  // Standard Calendar Quarter

      Mod(Month(Date_Sales) - $(vFiscalYearStartMonthSales), 12)+1 AS FiscalMonthSales,  // Fiscal Calendar Month

      'FY' & right(YearName(Date_Sales, 0, $(vFiscalYearStartMonthSales)),2) AS FiscalYearSales; // Fiscal Calendar Year

      LOAD

      Date($(vStartDateSales) + RangeSum(Peek('RowNumSales'), 1) - 1) AS Date_Sales,

      RangeSum(Peek('RowNumSales'), 1) AS RowNumSales

      AutoGenerate vEndDateSales - vStartDateSales + 1;