6 Replies Latest reply: Apr 19, 2012 4:49 AM by M Paeper RSS

    Custom weekly calendar creation - how to?

    M Paeper

      Hi,

       

      Have rummaged through the forums but found nothing that might help resolve this. We have a custom production calendar - the production weeks start on a Saturday and end on a Friday. At the moment I've created an inline table for the current year, but am hoping to figure out a way to automate this in script for historical as well as all future dates without needing to load or enter data. Effectively I'm thinking I want to determine a custom start and end day for a particular year to define week 1 and roll the weeks forward from there.

       

      I'm thinking of using the MakeWeekDate function and then mapping particular days of week to a particular week number for the current year but havent gotten far - because I get to ridiculous results like 7 Jan 2012 falling into weekending Friday 6 Jan 2012. i.e.

      CalDateErr.png

      I'd appreciate any idea on this or a better solution. Thanks

       

      Script so far is this. Many ideas sourced from http://www.rhyous.com/2009/11/20/how-to-create-a-calendar-in-qlikview/

      //---------------------------------------------------------------------------

       

      Calendar:

      LET vDateMin = Num(MakeDate(2011,1,1));

      LET vDateMax = Num(MakeDate(2012,12,31));

      LET vDateToday = Num(Today());

       

      TempCalendar:

      LOAD

      $(vDateMin) + RowNo() - 1 AS DateNumber,

      Date($(vDateMin) + RowNo() - 1) AS TempDate

      AUTOGENERATE 1

      WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

       

      Calendar:

      LOAD

      Date(TempDate) AS CalendarDate,

      Week(TempDate) AS CalendarWeek,

      Month(TempDate) AS CalendarMonth,

      Year(TempDate) AS CalendarYear,

      MakeWeekDate(Year(TempDate),Week(TempDate),4) as CalendarWeekEndingFriDate //4=Fri

      RESIDENT TempCalendar ORDER BY TempDate ASC;

       

      DROP TABLE TempCalendar; 

       

      //----------------------------------------------------------------------------

      // I have an inline calendar for 2012 looking like this (but its obviously not an automated future/historical date proof solution)

       

      [2012WEProdCal]:

      LOAD * INLINE [

          ProdCalWeek, CalendarWeekEndingFriDate, ProdCalMonth

          1, 06/01/2012, Jan

          2, 13/01/2012, Jan

          3, 20/01/2012, Jan

          4, 27/01/2012, Jan

          5, 03/02/2012, Feb

          6, 10/02/2012, Feb

          7, 17/02/2012, Feb

          8, 24/02/2012, Feb

          9, 02/03/2012, Feb

          10, 09/03/2012, Mar

          11, 16/03/2012, Mar

          12, 23/03/2012, Mar

          13, 30/03/2012, Mar

          14, 06/04/2012, Apr

          15, 13/04/2012, Apr

          16, 20/04/2012, Apr

          17, 27/04/2012, Apr

          18, 04/05/2012, May

          19, 11/05/2012, May

          20, 18/05/2012, May

          21, 25/05/2012, May

          22, 01/06/2012, May

          23, 08/06/2012, Jun

          24, 15/06/2012, Jun

          25, 22/06/2012, Jun

          26, 29/06/2012, Jun

          27, 06/07/2012, Jul

          28, 13/07/2012, Jul

          29, 20/07/2012, Jul

          30, 27/07/2012, Jul

          31, 03/08/2012, Jul

          32, 10/08/2012, Aug

          33, 17/08/2012, Aug

          34, 24/08/2012, Aug

          35, 31/08/2012, Aug

          36, 07/09/2012, Sep

          37, 14/09/2012, Sep

          38, 21/09/2012, Sep

          39, 28/09/2012, Sep

          40, 05/10/2012, Oct

          41, 12/10/2012, Oct

          42, 19/10/2012, Oct

          43, 26/10/2012, Oct

          44, 02/11/2012, Oct

          45, 09/11/2012, Nov

          46, 16/11/2012, Nov

          47, 23/11/2012, Nov

          48, 30/11/2012, Nov

          49, 07/12/2012, Dec

          50, 14/12/2012, Dec

          51, 21/12/2012, Dec

          52, 28/12/2012, Dec

          53, 04/01/2013, Jan

      ];

       

      //Attached I have a picture of what this calendar looks like for 2012. How can I algorithmically map the Dates from my QV script generated calendar onto the Production weeks so I can determine which date falls into which production week number.

      prodcal2012.png

      //and for 2011

      aprodcal2011.jpg

        • Custom weekly calendar creation - how to?
          M Paeper

          Did some more RTFM in the online help and found some more functions to test. I think I've solved the first part of my Q - defining the custom start and end dates of my week. It may however, be coincidence that its working because my vDateMin is 1 Jan 2011 which also happens to be a saturday which is the dayofweek I wish to start counting weeks from.

           

          Anyway, when I add the following 2 lines to my LOAD script

           

          WeekStart(TempDate,0,-2) as WeekStartingDate, // -2 is days offset from default QV calendar

          WeekEnd(TempDate,0,-2) as WeekEndingDate,

           

          I get to this. (I have left my old incorrect MakeWeekDate(Year(TempDate),Week(TempDate),4) as CalendarWeekEndingFriDate in the table for comparison purposes.

           

          prodcalstartend.png

           

          Given my WeekStartingDate and

          WeekEndingDate fields, the CalendarWeek value is incorrect.

           

          How can I modify the script to obtain Week numbers that correspond with my Production calendar?

          i.e. Week 1 of 2011 starts on 1 Jan 2011 and ends on 7 Jan 2011 and week 1 of 2012 starts on 31 Dec 2011 and ends on 6 Jan 2012.

            • Custom weekly calendar creation - how to?
              jagan mohan rao appala

              Hi,

               

              Use LunarWeekStart() and LunarWeekEnd() for this.

               

              Hope this helps you.

               

              Regards,

              jagan.

                • Re: Custom weekly calendar creation - how to?
                  M Paeper

                  Hi Jagan,

                   

                  Thanks for your input. I'll concede I'm not entirely clear when I should use LunarWeekStart vs WeekStart.

                   

                  I've tried both, and when I set vDateMin to a different earlier year where 1 Jan doesnt fall on my custom week start day of Sat, LunarWeekStart doesnt correspond to the production calendar I'm trying to replicate in script.

                   

                  Calendar:

                  LET vDateMin = Num(MakeDate(2010,1,1));

                  LET vDateMax = Num(MakeDate(2012,12,31));

                  LET vDateToday = Num(Today());

                   

                  TempCalendar:

                  LOAD

                  $(vDateMin) + RowNo() - 1 AS DateNumber,

                  Date($(vDateMin) + RowNo() - 1) AS TempDate,

                  LunarWeekStart(Date($(vDateMin) + RowNo() - 1),0,-0) as LunarWeekStartingDate,

                  LunarWeekEnd(Date($(vDateMin) + RowNo() - 1),0,-0) as LunarWeekEndingDate,

                  WeekStart(Date($(vDateMin) + RowNo() - 1),0,-2) as TempWeekStartingDate, // -2 is days offset from default Mon-Sun QV calendar

                  WeekEnd(Date($(vDateMin) + RowNo() - 1),0,-2) as TempWeekEndingDate

                  AUTOGENERATE 1

                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                   

                  //My results are:

                   

                  lunarvsotherweek.png

                  It seems for whatever reason WeekStart is giving me the answer that corresponds to the production calendar I wish to replicate.

                   

                  Can you suggest a way I can associate a week number in the script - i.e. week 1 to 52 (or 53 if need be) for each Year with the dates in the range of WeekStart to WeekEnd. I suspect it'll require a loop, but I havent figured out any working syntax yet.Thanks

                    • Re: Custom weekly calendar creation - how to?
                      jagan mohan rao appala

                      Hi,

                       

                      Hope I understood correctly, you need to arrive a field Week based on Jan 1.  For this Use LunarWeekName( ) to get the Week name based on Jan 1

                       

                      For ex:

                       

                      LunarWeekName('1/1/2012') will give 2012/01i.e, first week of 2012

                       

                       

                      There is no need to use loop.

                       

                      Regards,

                      Jagan.

                        • Re: Custom weekly calendar creation - how to?
                          M Paeper

                          Hi Jagan,

                           

                          Not exactly, my production weeks start on a Saturday and end on a Friday, so unless 1/1/2012 is a Saturday LunarWeek doesnt seem work for me.

                           

                          Now taking 2012 as an example year, for my production calendar, week 1 is from Sat 31 Dec 2011 - Fri 06 Jan 2012, then week 2 is from Sat 07 Jan 2012 - Fri 13 Jan 2012, ...

                           

                          How can I get the following (I am showing the overlap between years, since every year it needs to revert back to 1 for week 1 (and week 1 may contain a starting date that has the previous years date in it and a production week doesnt necessarily fall only into a signle month as in my 2012 example below) and count up to week 52 before reverting back to week 1). e.g. taking a sample period of data

                           

                          Week Number  |  WeekStartingDate | WeekEndingDate

                                    51         |  17/12/2011            | 23/12/2011

                                    52         |   24/12/2011           | 30/12/2011

                                    1           |  31/12/2011            | 06/01/2012

                                    2           |  07/01/2012            | 13/01/2012

                                    3           |  14/01/2012            |  20/01/2012

                                    4           |  21/01/2012            |  27/01/2012

                                    5           |  28/01/2012            |  03/02/2012

                                    6           |  04/02/2012            |  10/02/2012 ...

                           

                          I've marked the odd dates in (boldtext) so you can see the where dates dont correspond to std calendar norms.

                           

                          I'm thinking one way to do this in script would be to set all the RecNo()'s from vMinDate to the WeekEndingDate for RecNo(1) to week 1 and then the next 7 records after the first WeekEndingDate records to week 2, then the next 7 after that to week 3 ... etc until week 52 then start again on week 1 but I am unsure how to do this in script - I'm thinking possibly with a temporary table and then Peek() back at the previous record and its WeekEndingDate and then applying a Week Number calculation to that - something like check if previous record's WeekEndingDate is different from current record WeekEndingDate and if true then increment Week Number by 1 until WeekEndingDate falls into a new year then start with week numbers of 1 again - possibly using a While loop ... but not entirely sure how to implement such a calculation in practice. Thanks

                  • Re: Custom weekly calendar creation - how to?
                    M Paeper

                    Have finally figured out a solution for the custom weekly calendar part here. Havent resolved the custom production months and year parts yet though, so ideas appreciated. Thanks

                     

                    http://community.qlik.com/message/211578#211578