6 Replies Latest reply: Mar 8, 2013 1:13 PM by Brian Garside RSS

    Min/Max field values not working in Master Calendar

    Brian Garside

      I have a date value that I pulled the MaxRevMonth & MinRevMonth values from a resident table and want to use them in my Master Calendar.

      For some reason it errors out saying "Field not found". Seems like a simple thing, what I'm I missing ?

       

      errr.jpg

       

      ForecastRev:
      LOAD date(floor("Report EOM")) as [Report EOM Rev] ,
      date(floor(ReportRundate)) as [ReportRundate_Rev],
      Project,
      date(floor(RevMonth)) as RevMonth,
      //date(floor(Max(RevMonth))) as MaxRevMonth,
          //date(floor(Min(RevMonth))) as MinRevMonth,
          RevAmt,
      RevType,
      WorkLocation,
      BacklogType as [BacklogType_Rev];
      //group by Project;
      SQL SELECT "Report EOM",
      ReportRundate,
      Project,
      RevMonth,
      RevAmt,
      RevType,
      WorkLocation,
      BacklogType
      FROM CorpReporting.MonthlyBacklogForecastRev;

      //Concatenate (ForecastRev)

      MaxMinRevMonth:
      Load

      date(floor(Max(RevMonth))) as MaxRevMonth,
      date(floor(Min(RevMonth))) as MinRevMonth

      Resident ForecastRev;

       

      //****************************************************** New Tab

       

      LET vDateMin = MinRevMonth;              
      LET vDateMax = MaxRevMonth;         

      TempCalendar: 
      LOAD
      $(vDateMin) + RowNo() - 1 AS DateNumber
      Date($(vDateMin) + RowNo() - 1) AS TempDate 
      AUTOGENERATE
      WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

      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,