0 Replies Latest reply: Apr 22, 2014 9:31 AM by Mattias Stenhamre RSS

    Calendar week cut-off

    Mattias Stenhamre

      I am very much a newbie and I am trying to figure out how to get the yearly cut-off dates correct in my calendar. Here is my pre-defined data-set that I have received help building;

       

      I am using  a week-counter for the period in between the below two dates. Basically:

      ArticleStartDate = Mon Jan 1st

      ArticleEndDate = Mon Jan 14th

      = 2 weeks, with count of 1 for calendar week 1 and 2 respectively.

       

      Using this logic we have defined the calendar below to not count any week with an ArticleStartDate on a Wednesday or later as current week. Please find the script relating to this below. My problem is this; Any week-count is correct using this logic up until the cut-off between a Fiscal Year or Fiscal Quarters. Good example is this; Our fiscal year ends 30 Sept. This date occurred on a Monday in 2013. I would like to use the same logic for this cut-off as for the ArticleStartDate and ArticleEndDate, basically the week starting on Monday Sept 30 should be included in Fiscal year 2014/2015 instead of (as it is now) 2013/2014 (i.e. Tuesday Oct 1st would count as StartDate and since this is before Wednesday it should therefore be counted as current week being First week of FY 2014/2015).

       

      If anyone can find the time to help me with this problem I would be forever grateful

      Best regards

      /Mattias

       

      //*************WEEK COUNTER******************

           if(weekday(ArticleStartDate) > 2, Date(Floor(ArticleStartDate)) + 7, Date(Floor(ArticleStartDate))) as StartDate_QV,

           if(weekday(ArticleEndDate) < 3, Date(Floor(ArticleEndDate)) - 7, Date(Floor(ArticleEndDate))) as EndDate_QV

       

      NoConcatenate load Distinct

             *,

             week(StartDate) as Week_Transaction,

             weekstart(Date(StartDate_QV + IterNo()-1 )) as ReferenceDate,

             weekstart(Date(StartDate_QV + IterNo()-1 )) as %DateID,

       

       

        num(WeekDay(StartDate_QV)) as Day1,

           1 as Counter,

           1 as Counter_Week

      Resident [Main Data Temp]

      while (IterNo() <= (num([EndDate_QV]) - num([StartDate_QV])) + 1 )

       

      //**********CALENDAR************

      MinDate:

      LOAD

        ArticleStartDate as MinDate.ArticleStartDate

      Resident [Main Data]

      Order by ArticleStartDate asc;

       

      LET vMinDate = NUM(PEEK('MinDate.ArticleStartDate', 0, 'MinDate'));

      drop table MinDate;

       

      MaxDate:

      LOAD

        ArticleEndDate as MinDate.ArticleEndDate

      Resident [Main Data]

      Order by ArticleEndDate asc;

       

      LET vMaxDate = NUM(PEEK('MinDate.ArticleEndDate', -1, 'MaxDate'));

      drop table MaxDate;

       

      SET vLFM = 3; // 3 = three remaining months in calendar year

       

      // Step 2: Create temporary calendar: one row for each tday

      Calendar_Temp:

      LOAD

        num($(vMinDate) + ROWNO() - 1) AS Datefield,

        addmonths(num(($(vMinDate) + ROWNO() - 1)), + $(vLFM)) AS Datefield_FY

      AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

       

      // Step 3: Create final calendar

      Calendar:

      LOAD

      // Dates

        Datefield,

        Datefield as %DateID,

        Datefield_FY,

       

      // Year

        year(Datefield_FY) as Year_FY,

        year(Datefield) as Year_CY,

       

      // Week

        week(Datefield_FY) as Week_FY,

        week(Datefield) as Week_CY,

        if(Week(Datefield)>40,Week(Datefield)-40,Week(Datefield)+15) as Week_CY_Sequence,

       

      // yearname(Datefield,0,10) as YearName_FY,

        num(month(Datefield_FY),00) as Period_FY,

       

        MONTH(Datefield) AS Month,

       

        day(Datefield) as Day,

        WeekDay(Datefield) as WeekDay,

        num(Month(Datefield),00) AS MonthNum,

        year(Datefield) &'-'& month(Datefield) as YearMonth,

        year(Datefield) &'-'& num(month(Datefield),00) as YearMonthNum,

        'Q' & ceil(Month(Datefield)/3) AS Quarter_CY,

        'Q' & ceil(Month(Datefield_FY)/3) AS Quarter_FY,

        Year(Datefield) & '-Q' & ceil(Month(Datefield)/3) AS YearQuarter

      RESIDENT Calendar_Temp;

       

      drop tables Calendar_Temp;