4 Replies Latest reply: Jun 4, 2013 5:26 PM by rberk123 RSS

    Different fiscal year from calender year

    Thomas Eyck

      Dear all,

      I am loading excel files with data containing a date (dd.mm.yyyy). from January until december. I use month() and year() to seperate the parts of the date and create listboxes to display the values.

      But then I want to calculate the sum(value) not of the calender year which is QV using but I want to calculate sums from May to April, a different fiscal year.

      All my formulars are using the calender year but I want to "force" them to uses a different time setting.

      Is there a possible was to use a calender from May to April, e.g. May 2010 - April 2011, May 2001 - April 2012 and so on.

      Thanks in advance

      Thomas

        • Different fiscal year from calender year

          Yes this is possible by "shifting" dates in your date dimension. Below is an example of a calendar i recently implemented where the fiscal year starts the first of september. This is done by "shifting" the dates 4 months forward. (in other words the first of september is seen as the first day of the first month of a fiscal year).

          In your situation you either have to shift time backwards by 4 months or forward by 8 (this depends on how you want to call your fiscal year).

          Here is the calendar you can use as a reference:

          LET vDateMin = Num(MakeDate(2008,9,1));
          LET vDateMax = Floor(AddMonths(YearEnd(AddMonths(Today(),4)),-4));

          CalendarDates:
          LOAD $(vDateMin) + RowNo() - 1 AS DateNumber
          , Date($(vDateMin) + RowNo() - 1) AS TempDate
          AUTOGENERATE 1
          WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

          Calendar:
          LOAD TempDate AS %Key_Date
          , TempDate AS Date
          //, Day(TempDate) AS Day
          //, WeekDay(TempDate) AS WeekDay
          , Num(Week(TempDate),00) AS Week
          , Month(TempDate) AS Month
          , Num(Month(TempDate),00) AS MonthNum
          , Year(TempDate) AS Year
          , 'Q' & Ceil(Month(TempDate)/3) AS Quarter
          , WeekYear(TempDate) & '-w' & Num(Week(TempDate),00) AS YearWeek
          , Year(TempDate) & '-' & Num(Month(TempDate),00) AS YearMonth
          , Year(TempDate) & '-Q' & Ceil(Month(TempDate)/3) AS YearQuarter
          , Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum
          , 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) AS FiscalYear
          , 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) & ' Q' & Ceil(Month(AddMonths(TempDate,4))/3) AS FiscalYearQuarter
          RESIDENT CalendarDates
          ORDER BY TempDate ASC;

          DROP TABLE CalendarDates;

            • AW:Re: Different fiscal year from calender year
              Thomas Eyck

              Hi Toine Rozemeijer, I wil ltry it and see hiw it works. Thanks.

              • Re: Different fiscal year from calender year

                Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum

                , 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) AS FiscalYear

                , 'FY ' & Right(Year(AddMonths(TempDate,-8)),2) & '-' & Right(Year(AddMonths(TempDate,4)),2) & ' Q' & Ceil(Month(AddMonths(TempDate,4))/3) AS FiscalYearQuarter

                 

                I was searching around and was looking to add a Fiscal Calendar to my Master.  I liked what you had here but made a slight change. In the line " Num(Month(AddMonths(TempDate,4)),00) AS FiscalMonthNum".  I changed the 4 to a 3. It appeared that it was not labeling the months correctly to the Fiscal Quarter

                 

                thank you

                 

                rb

              • Different fiscal year from calender year
                Sandro Pividori

                HI.

                In the script you could set Month-Year field as follows:

                Month(Date)&'-'&year(Date) as MonthYear

                and use it to select the period.

                Hope this helps.

                Regards.