Hi All,
I have the following Master Calendar that has been working fine until now:
Set vFM = 9; // First month of fiscal year
Set vFD = 4; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
MasterCalendar:
Load Distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year ex. 2015/16
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil(([EventStartDate]-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
[EventStartDate],
YearStart([EventStartDate],0,$(vFM)) as FYearStart,
Year([EventStartDate]) as Year,
Month([EventStartDate]) as Month,
Date(Monthstart([EventStartDate]), 'MMM-YYYY') as MonthYear,
Week([EventStartDate]) as ISOWeekNo,
Dual(WeekDay([EventStartDate]),Mod(WeekDay([EventStartDate]-$(vFD)),7)+1) as WeekDay,
Day([EventStartDate]) as Day,
Date([EventStartDate], 'MM/DD') as DATEMMDD
Resident Events;
The challenge I now face is dealing with date formats outside the United States. We store the dates in the Database like it would display in our clients environment. The problem with this is when I run it through the event calendar above a date store as 12/6/2014 which is actually 6/12/2014 is converted to a fiscal year of 2014/2015 when it is actually 2013/2014. I hope this makes sense. Please let me know and I will try to explain it differently if need be.
Thanks
David
