Financial Year vs Calendar

[#1]

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.

[hic]

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;

[lawrenceiow]

Excellent, just what I needed, thank you.

I especially like the idea of using Dual so that the months are sorted correctly.