Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Accepted Solutions
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.
.png "Luminary Alumni"){kind=icon}
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