I use this code in my master calendar to get the Fiscal Year and Period from the calendar date (CalDate / CalMonth / CalYear):
// Fiscal year fields - GL Year
CalYear + If(CalMonth < $(zGLYearStartMonth), 0, 1) As GLYear,
// Fiscal year fields - GL Period
If (CalMonth >= $(zGLYearStartMonth),
If(CalDay > $(zLastDOM), CalMonth - $(zGLYearStartMonth) + 2, CalMonth - $(zGLYearStartMonth) + 1),
If(CalDay > $(zLastDOM), CalMonth + (12 - $(zGLYearStartMonth) + 2), CalMonth + (12 - $(zGLYearStartMonth) + 1))) As GLPeriodM,
- zGLYearStartMonth first month of fiscal year
- zLastDOM last day of the month of fiscal period (set to 31 or greater if the fiscal periods correspond to calendar months)
Attached is a calendar. in the calendar tab is a variable in which you change the first month of a fiscal year
remember to set the Calendar Type to "always only 1 selected"
essentially you are duplicating your master calendar for different calendar types and then selecting the calendar type you want to work with.
Since all your time data calculations and graphs will work with the new YEAR and MONTH set up - things will automatically change based on the calendar type selections.
hope this helps,
Calendar.qvw 154.5 K
you put the following field in your transactions table (or whatever table contains the date):
Year(date(SaleDate))&num(Month(date(SaleDate)),'00') as YM,
then you can add this statement at the end of your load script (or anytime after the table containing YM)
//Create fiscal year start month
set vFiscalMonth = 8;
//create temp calander for all the distinct year month dates in your data
load distinct YM,
num(right(YM,2)) as TMonth,
Month(MakeDate(Left(YM,4),right(YM,2) , 1)) As MONTH
num(left(YM,4)) as YEAR,
TMonth as MonthSort,
'Q'&(if(num(right(YM,2))<4,1,if(num(right(YM,2))<7,2,if(num(right(YM,2))<10,3,if(num(right(YM,2))<13,4))))) as Quarter,
'Calendar' as CalendarType
//Duplicates calendar but for Fiscal Year
if(TMonth<$(vFiscalMonth),num(left(YM,4)),num(left(YM,4))+1) as YEAR,
if(TMonth<$(vFiscalMonth),TMonth+12-$(vFiscalMonth)+1,TMonth-$(vFiscalMonth)+1) as MonthSort,
'Q'&(if(num(right(YM,2))<4,4,if(num(right(YM,2))<7,1,if(num(right(YM,2))<10,2,if(num(right(YM,2))<13,3))))) as Quarter,
'Fiscal' as CalendarType
Drop Table TCAL;
Once you are using your data in the front end, remember to use YEAR and MONTH when you deal with date information.
NB: create a listbox for CalendarType - select 1 , then right click properties and change to "Always only 1 selected" - this is because multiple calendars duplicate data due to there being both a Fiscal YEAR MONTH and a Calendar YEAR MONTH pointing to the same data so you want to restrict it to only 1 set at all times.
hope this helps.. let me know if you have problems
p.s. The MonthSort field is there for if you want to make the list boxes sort the order based on the calendar type. so for fiscal it will go month AUG,SEP,OCT,NOV,DEC,JAN,...JUL and for normal Calendar it will go JAN,FEB,MAR...DEC - also handy for when you make charts.