Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Excellent, just what I needed, thank you.
I especially like the idea of using Dual so that the months are sorted correctly.