2 Replies Latest reply: Sep 26, 2017 6:34 AM by Arvind Patil RSS

    Current Fiscal Month Number

    Phil Speight

      Hi

       

      Can anyone let me know how to return the current Fiscal Month Number based on todays date.

       

      26 September 17 = 6

       

      14 May 17 = 2

       

      Etc

       

       

      Many thanks

       

       

       

      Phil

        • Re: Current Fiscal Month Number
          Arvind Patil

          Hi Phil,

           

          Please Find below Script:

           

          It will  work For you:

          SET vFiscalYearStartMonth = 4;

          LET vStartDate = Num(YearStart(Today(), -1));

          LET vEndDate = Num(AddYears(Today(),1));

           

          FiscalCalendar:

          LOAD

          *,

          Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

          Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

          LOAD

          *,

          Year(Date) AS Year, // Standard Calendar Year

          Month(Date) AS Month, // Standard Calendar Month

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

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

          Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

          YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

          LOAD

          Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

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

          AutoGenerate vEndDate - vStartDate + 1;

           

          Thanks,

          Arvind Patil