2 Replies Latest reply: Feb 12, 2015 4:19 PM by swarup malli RSS

    Quarters in Master calender

    Balraj Prabhu
      I found the following routine to create a master calendar written by Rob Wunderlich.
      When I try to tweak it to create quarters ,it fails ( I have highlighted the code in  black ).
      Need assistance with creating quarters using this routine
      //===================================================
      // Subroutine to Generate Calendar.
      //===================================================
      SUB CalendarFromField(_field, _calendar, _prefix)
      [$(_calendar)]:
      // Generate Final Calendar
      Load
      'Q' &
      ceil((mod((Month([$(_field)])+8),12)+1)/3) as[$(_prefix)Quarter1],
      'Q' &
      pick(Month([$(_field)]),4,4,4,1,1,1,2,2,2,3,3,3)as[$(_prefix)Quarter]
      ;
      LOAD
      [$(_field)]
      ,
      year([$(_field)]) as [$(_prefix)Year]
      ,
      month([$(_field)]) as [$(_prefix)Month]
      ,
      day([$(_field)]) as [$(_prefix)Day]
      ,
      weekday([$(_field)]) as [$(_prefix)Weekday]
      // 'Q' & ceil(month([$(_field)]) / 3) as[$(_prefix)Quarter]
      ;
      // Generate range of dates between min and max.
      LOAD
      date(DateMin + IterNo()) as [$(_field)] // Link Field
      WHILE DateMin + IterNo() <= DateMax
      ;
      // Find min and max of date field values.
      LOAD
      min(datefield)-1 as DateMin
      ,
      max(datefield) as DateMax
      ;
      // Load date field values.
      LOAD
      FieldValue('$(_field)', RecNo()) as datefield
      AutoGenerate FieldValueCount('$(_field)');

      END SUB
        • Re: Quarters in Master calender
          swarup malli

          Try this:

          'Q' & ceil(Num(month([$(_field)]))/3) as [$(_prefix)Qtr];


          • Re: Quarters in Master calender
            swarup malli


            LOAD
            [$(_field)]
            ,
            year([$(_field)]) as [$(_prefix)Year]
            ,
            month([$(_field)]) as [$(_prefix)Month]
            ,
            day([$(_field)]) as [$(_prefix)Day]
            ,
            weekday([$(_field)]) as [$(_prefix)Weekday],
            'Q' &
            ceil(Num(month([$(_field)]))/3) as [$(_prefix)Qtr]
            ;
            // Generate range of dates between min and max.
            LOAD
            date(DateMin + IterNo()) as [$(_field)] // Link Field
            WHILE DateMin + IterNo() <= DateMax
            ;
            // Find min and max of date field values.
            LOAD
            min(datefield)-1 as DateMin
            ,
            max(datefield) as DateMax
            ;
            // Load date field values.
            LOAD
            FieldValue('$(_field)', RecNo()) as datefield
            AutoGenerate FieldValueCount('$(_field)');

            END SUB