My fiscal year goes from August 10 July, however when displaying sales data for fiscal year as soon as I the year it only displays from January onwards (no Aug-Dec data). If I then select the year and Select August thru Dec either individually or together the correct sales data is return however as soon as I select Jan, August to Dec data no longer appears. I have not had much experience with calendars and I would say the issue is with my script.
CalendarVariables:
LET vDateMin = Num(MakeDate(2009,1,1));
LET vDateMax = Floor(MonthEnd(Today()));
LET vDateToday = Num(Today());
LET vFiscalStartMonth = 8;
QuarterMap:
mapping load
rowno() as Month,
'Q' & ceil(rowno()/3) as Quarter
autogenerate 12;
FiscalQuarterMap:
mapping load
rowno() as Month,
'Q' & ceil(month(addmonths('1/' & rowno() & '/2000',1-$(vFiscalStartMonth)))/3) as Quarter
autogenerate 12;
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
//Standard or Fiscal Calendar
Calendar:
LOAD
Date(TempDate) AS CalendarDate,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,
WeekDay(TempDate) AS CalendarDayName,
Week(TempDate) AS CalendarWeekOfYear,
Month(TempDate) AS CalendarMonthName,
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
Year(TempDate) AS CalendarYear,
// Fiscal Date Objects
Yearname(TempDate,0,$(vFiscalStartMonth)) as FiscalYear,
If(TempDate >= YearStart('$(vToday)', -2, $(vFiscalStartMonth)), YearName(TempDate,0,$(vFiscalStartMonth))) as FiscalYear3YrHistory,
If(Index(Yearname(TempDate,0,$(vFiscalStartMonth)),'-')>0, subfield(yearname(TempDate,0,$(vFiscalStartMonth)),'-',2), Year(TempDate)) as FiscalBudgetYear,
ApplyMap('FiscalQuarterMap', Month(TempDate)) as FiscalQuarter,
Dual(Month(TempDate),Month(AddMonths(TempDate,1-$(vFiscalStartMonth),0))) as FiscalMonth,
If(TempDate>=yearstart('$(vToday)',-2,$(vFiscalStartMonth)), Dual(Month(TempDate),month(addmonths(TempDate,1-$(vFiscalStartMonth),0)))) as FiscalMonth3YrHistory,
Monthname(TempDate) as RollMonth,
If(TempDate>=Yearstart('$(vToday)',-2,$(vFiscalStartMonth)),monthname(TempDate)) as RollMonth3YrHistory,
//Calendar ID's - used in Set Analysis
(Year(TempDate) -1) * 12 + Num(Month(TempDate)) As CalendarMonthID,
Right(applymap('QuarterMap',month(TempDate)),1) + 75 + (Year(TempDate)) + 6000 As CalendarQuarterID,
(Year(TempDate)) + 6000 As CalendarYearID,
// Calendar Date Names
WeekName(TempDate) as CalendarWeekNumberAndYear,
MonthName(TempDate) as CalendarMonthAndYear,
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,
// Start Dates
DayStart(TempDate) as CalendarDayStart,
WeekStart(TempDate) as CalendarWeekStart,
MonthStart(TempDate) as CalendarMonthStart,
QuarterStart(TempDate) as CalendarQuarterStart,
YearStart(TempDate) as CalendarYearStart,
// End Dates
DayEnd(TempDate) as CalendarDayEnd,
WeekEnd(TempDate) as CalendarWeekEnd,
MonthEnd(TempDate) as CalendarMonthEnd,
QuarterEnd(TempDate) as CalendarQuarterEnd,
YearEnd(TempDate) as CalendarYearEnd,
// Combo Date Examples
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());
I have attached my results it could be something on the model itself but that is fairly straight forward so I do not think that is the problem.
