Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Custom weekly calendar creation - how to?

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

1 Solution

Accepted Solutions
Not applicable

Re: Custom weekly calendar creation - how to?

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

6 Replies
Not applicable

Custom weekly calendar creation - how to?

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.

jagan
Not applicable

Custom weekly calendar creation - how to?

Hi,

Use LunarWeekStart() and LunarWeekEnd() for this.

Hope this helps you.

Regards,

jagan.

Not applicable

Re: Custom weekly calendar creation - how to?

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

jagan
Not applicable

Re: Custom weekly calendar creation - how to?

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.

Not applicable

Re: Custom weekly calendar creation - how to?

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

Not applicable

Re: Custom weekly calendar creation - how to?

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