2 Replies Latest reply: Apr 20, 2015 7:08 AM by Lawrence Cooper RSS

    Financial Year vs Calendar

      I would like to be able to give my users a facility to display their data for financial years rather than calendar. My financial year is Aug-Jul. I have a datestamp field which I extract Year() and Month() from. I have two list boxes with months and years but what I would like to have in the Year box is my financial years i.e. [2010-11], [2011-12] etc. How can I achieve this? Your help would be greatly appreciated.

        • Re: Financial Year vs Calendar
          Henric Cronström

          You should create this in a Master Calendar. See example below.

           

          All you need is some kind of Transaction table with a field called "date". The sample creates Years with values like 2010/2011 and months with standard month names - but sorted correctly.

           

          HIC

           

          Set vFirstMonthOfFiscalYear = 4;

           

          MasterCalendar:

          Load distinct *,

                    if(Month>FiscalMonth,Year & '/' & (Year+1), (Year-1) & '/' & Year) as FiscalYear;

          Load *,

                    Dual(Month,Mod(Month-$(vFirstMonthOfFiscalYear),12)) as FiscalMonth;

          Load

                    date,

                    Year(date) as Year,

                    Month(date) as Month,

                    Date(MonthStart(date),'YYYY MMM') as RollingMonth

                    resident Transactions;