3 Replies Latest reply: Jun 24, 2014 7:14 AM by David Young RSS

    Fiscal Year Question

    David Young

      I have the following information in my load statement:

       

      MasterCalendar:

      LOAD DISTINCT

             [Request Date],

             date([Request Date]) AS DATE,

             //AddYears(date([Request Date]),1) AS [Request Date],

       

      //     Dates 

             year([Request Date]) AS Year,

             month([Request Date]) AS Month,

             date(monthstart([Request Date]), 'MMM-YYYY') AS MonthYear,

             'Q'&Ceil(Num(Month([Request Date])/3)) as Quarter,

             week([Request Date]) AS Week,

             weekday([Request Date] + 2) AS Weekday,

             day([Request Date]) AS Day,

             date([Request Date], 'MM/DD') AS DATEMMDD

       

      My question is this works based on Calendar Year.  I want to base it on Fiscal Year.  My guess I can add a line above the MasterCalendar like:

       

      Set vFM = 6 ;                                                      // First month of fiscal year

      I am a little lost what the next steps are.  I have read over a ton of examples but still not sure.  Can someone help me out.

       

      David

        • Re: Fiscal Year Question
          Jean-Pierre Bakhache

          Hello David,

           

          Did you check the solution proposed under:

          http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year

            • Re: Fiscal Year Question
              David Young

              Hi Jean-Pierre -

               

              I have read over the link you provided.  I have not been successful with the whole Master Calendar idea and have not used it much. I have updated my Master Calendar Tab in the load statement to the following:

               

              Set vFM = 6 ;                                                          // First month of fiscal year

              Calendar:

              Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year
                       Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

                        *;

              Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

                       Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month


              //     Dates 

                     year([Request Date]) AS Year,

                     month([Request Date]) AS Month,

                     date(monthstart([Request Date]), 'MMM-YYYY') AS MonthYear,

                     'Q'&Ceil(Num(Month([Request Date])/3)) as Quarter,

                     week([Request Date]) AS Week,

                     weekday([Request Date] + 2) AS Weekday,

                     day([Request Date]) AS Day,

                     date([Request Date], 'MM/DD') AS DATEMMDD

               

              The article mentions doing the same or Quarter, Week, etc.  This where I am still confused.  Any help would be great. 



            • Re: Fiscal Year Question
              Satyadev Jaiswal

              Hi,

               

              Below script may help you to create fiscal year.

               

              Left(YearName([Request Date], 0,6),4) as FiscalYear

               

              Thanks

              Satya