Hi,
I have a fiscal calendar setup (see below) which displays APR-MAR. When I select December the Voucher Date filters my table
(GLTransaction) by month 12. Can someone show me how to have Voucher Date filter by fiscal period month 9.
Thanks in advance
Set vFM = 4 ; // First month of fiscal year
Set vFD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
Set vFyear = ' ';
MasterCalendar:
Load Distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual(Month, fMonth)& fYear As FMonthYear,
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil((VoucherDate-StartOfFWeekOne+1)/7) as FWeekNo,
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,
WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,
*;
Load
Date(Date#(VoucherDate,'YYYYMM'),'MM/YYYY') as VoucherDate,
YearStart(VoucherDate,0,$(vFM)) as FYearStart,
Year(Date(Date#(VoucherDate,'YYYYMM'),'YYYYMM')) as Year,
Month(Date(Date#(VoucherDate,'YYYYMM'),'YYYYMM')) as Month,
Date(Monthstart(VoucherDate), 'MMM-YYYY') as MonthYear,
Week(VoucherDate) as ISOWeekNo,
Dual(WeekDay(VoucherDate),Mod(WeekDay(VoucherDate-$(vFD)),7)+1) as WeekDay,
Day(VoucherDate) as Day,
Date(VoucherDate, 'MM/DD') as DATEMMDD
Resident GLTransaction;
