6 Replies Latest reply: Oct 7, 2013 8:45 AM by Nirmal Raj RSS

    Fiscal Calendar


      Hi

      I have the following code for generating the Fiscal Calendar.

      This is working fine for me.

      But for the Fiscal Qaurter, i need the format of FY&Quarter to be shown, Like FY13 Q1 or 2013 Q1 something like that.

      Can you please help me?


      Set vFirstMonthOfFiscalYear = 11;

      Load distinct *,
      if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;

      Load *,

               
      Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
         
      Load

               
      Reporting_Date,

               
      Year(Reporting_Date) as CYear,

               
      Month(Reporting_Date) as CMonth,

               
      week(Reporting_Date) as CWeek,

               
      Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

                'Q'&
      ceil(Month(Reporting_Date)/3) as CQuarter,

      'Q'&

      If(Match(Month(Reporting_Date),'Nov','Dec','Jan') > 0 , 1,

      If(Match(Month(Reporting_Date),'Feb','Mar','Apr') > 0 , 2,

      If(Match(Month(Reporting_Date),'May','Jun','Jul') > 0 , 3,

      If(Match(Month(Reporting_Date),'Aug','Sep','Oct') > 0 , 4)))) as FiscalQuarter


      resident Volume;

       

        • Re: Fiscal Calendar
          Brice SACCUCCI

          Hi,

           

          can you try the following?

           

          Set vFirstMonthOfFiscalYear = 11;

          Load distinct *,
          if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;
          Load *,

                   
          Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
             
          Load

                   
          Reporting_Date,

                   
          Year(Reporting_Date) as CYear,

                   
          Month(Reporting_Date) as CMonth,

                   
          week(Reporting_Date) as CWeek,

                   
          Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

                    'Q'&
          ceil(Month(Reporting_Date)/3) as CQuarter,

          'FY' &
          Year(Reporting_Date) & ' Q' &

          If(Match(Month(Reporting_Date),'Nov','Dec','Jan') > 0 , 1,

          If(Match(Month(Reporting_Date),'Feb','Mar','Apr') > 0 , 2,

          If(Match(Month(Reporting_Date),'May','Jun','Jul') > 0 , 3,

          If(Match(Month(Reporting_Date),'Aug','Sep','Oct') > 0 , 4)))) as FiscalQuarter


          Regards,

          Brice

          • Re: Fiscal Calendar

            Hi,

             

            Try This

             

            Set vFirstMonthOfFiscalYear = 11;

            Load distinct *,
            if(CMonth>FMonth, (CYear+1),  CYear) as FiscalYear;

            Load *,

                     
            Mod(CMonth-$(vFirstMonthOfFiscalYear),12) as FMonth;
               
            Load

                     
            Reporting_Date,

                     
            Year(Reporting_Date) as CYear,

                     
            Month(Reporting_Date) as CMonth,

                     
            week(Reporting_Date) as CWeek,

                     
            Date(MonthStart(Reporting_Date),'MMM YY') as FiscalMonth,

                      'Q'&
            ceil(Month(Reporting_Date)/3) as CQuarter,


                     Year(Reporting_Date) &' '& 'Q'& ceil(Month(Reporting_Date)/3) as FiscalYearQuarter

            resident Volume;