topic Re: Custom weekly calendar creation - how to? in QlikView

Custom weekly calendar creation - how to?

Fri, 09 Mar 2012 11:34:06 GMT

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.

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.

//and for 2011

Custom weekly calendar creation - how to?

Fri, 09 Mar 2012 12:31:08 GMT

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.

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 — Fri, 09 Mar 2012 12:51:45 GMT

Hi,

Use LunarWeekStart() and LunarWeekEnd() for this.

Hope this helps you.

Regards,

jagan.

Re: Custom weekly calendar creation - how to?

Fri, 09 Mar 2012 14:44:08 GMT

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:

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 — Fri, 09 Mar 2012 15:16:27 GMT

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?

Mon, 12 Mar 2012 08:14:51 GMT

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?

Thu, 19 Apr 2012 08:45:25 GMT

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