1 Reply Latest reply: May 15, 2012 12:43 AM by Vipin Mishra RSS

    Fiscal Calendar Issue

    Kerryn Cane
      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
      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.