Calendar script shows 53 weeks than 52

mja_jami · ‎2019-02-05

Hello Qlik Experts,

I’m in a difficult situation that my calendar shows 53 weeks than 52 in a year. I’ve used generic calendar script that I’ve found and edited to populate customized weeks as I’ve customized week that starts on every Fridays and ends on every Thursdays. With the below script I was able to generate fields, i.e.;

CalendarMaster:

LOAD

Date(%Date) AS %Date,

Date(%Date) AS Date,

Year(%Date) AS Year,

WeekDay(%Date) as WeekDay,

Monthname(%Date) as MonthYear,

Month(%Date) As Month,

Day(%Date) As Day,

WeekStart([%Date],0,4) as WeekStart,

Num( WeekStart([%Date],0,4)) as numWeekStart,

WeekEnd([%Date],0 ,4 ) as WeekEnd,

num(Month([%Date])) as MonthNo,

date(floor(monthend([%Date]))) as MonthEndDate,

(Year([%Date]) * 100) + num(Month([%Date])) as YearMonthNo,

(Year([%Date]) * 12) + num(Month([%Date])) as YearMonthSeq,

'Q' & Ceil(Month(%Date) / 3) AS Quarter,

Year(%Date)&''&NUM(Month(%Date),'00') AS YearMonth,

Week(%Date) As Calendar_Week,

Weekname(%Date) as SP_Week_No

;

Load Date(MinDate + IterNo() -1 ) AS %Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

Min(%Date) AS MinDate,

Max(%Date) AS MaxDate

RESIDENT linktabletemp

Order by %Date;

left Join(CalendarMaster)

Load min(WeekStart) as FirstWeek,

Year

Resident CalendarMaster

Group by Year;

Left Join(CalendarMaster)

Load *,

(Year(WeekStart) * 100) + WeekNo as YearWeekNo;

Load ((WeekStart-FirstWeek)/7) + 1 as WeekNo,

WeekStart

Resident CalendarMaster;

Production_date is the data field that I want to plot against all the time dimension.

Now, Problems are:

WeekDay doesn’t match, such as 1/1/2019 was Tuesday not Sat.
Using customized Calendar such as:

week#	Week starts	Week ends
51	12/16/2016	12/22/2016
52	12/23/2016	12/29/2016
1	12/30/2016	1/5/2017
52	12/22/2017	12/28/2017
1	12/29/2017	1/4/2018
2	1/5/2018	1/11/2018

any date between 12/21/2018 - 12/27/2018 must show week 52 of the year 2018, similarly; dates between 12/28/2018 - 1/3/2019 must show week 1 of year 2019 but it shows:

%Date

MonthYear

Day

WeekDay

WeekStart

production_date

WeekEnd

Calendar_Week

WeekNo

SP_Week_No

YearWeekNo

MonthEndDate

Quarter

12/31/2018

Dec 2018

31

Fri

12/28/2018

12/31/2018

1/4/2019

53

2018/52

201853

12/31/2018

Q4

12/31/2018

Dec 2018

31

Fri

12/28/2018

12/31/2018

1/4/2019

53

1

2018/52

201801

12/31/2018

Q4

12/30/2018

Dec 2018

30

Thu

12/28/2018

12/30/2018

1/4/2019

53

2018/52

201853

12/31/2018

Q4

12/30/2018

Dec 2018

30

Thu

12/28/2018

12/30/2018

1/4/2019

53

1

2018/52

201801

12/31/2018

Q4

12/28/2018

Dec 2018

28

Tue

12/28/2018

1/4/2019

52

53

2018/51

201853

12/31/2018

Q4

12/28/2018

Dec 2018

28

Tue

12/28/2018

1/4/2019

52

1

2018/51

201801

12/31/2018

Q4

12/27/2018

Dec 2018

27

Mon

12/21/2018

12/27/2018

12/28/2018

52

2018/51

201852

12/31/2018

Q4

12/26/2018

Dec 2018

26

Sun

12/21/2018

12/26/2018

12/28/2018

52

2018/51

201852

12/31/2018

Q4

12/25/2018

Dec 2018

25

Sat

12/21/2018

12/25/2018

12/28/2018

52

2018/51

201852

12/31/2018

Q4

12/24/2018

Dec 2018

24

Fri

12/21/2018

12/24/2018

12/28/2018

52

2018/51

201852

12/31/2018

Q4

12/22/2018

Dec 2018

22

Wed

12/21/2018

12/22/2018

12/28/2018

51

52

2018/50

201852

12/31/2018

Q4

12/21/2018

Dec 2018

21

Tue

12/21/2018

12/28/2018

51

52

2018/50

201852

12/31/2018

Q4

12/20/2018

Dec 2018

20

Mon

12/14/2018

12/20/2018

12/21/2018

51

2018/50

201851

12/31/2018

Q4

1/1/2019

Jan 2019

1

Sat

12/28/2018

1/1/2019

1/4/2019

1

53

2018/52

201853

1/31/2019

Q1

1/1/2019

Jan 2019

1

Sat

12/28/2018

1/1/2019

1/4/2019

1

2018/52

201801

1/31/2019

Q1

1/2/2019

Jan 2019

2

Sun

12/28/2018

1/2/2019

1/4/2019

1

53

2018/52

201853

1/31/2019

Q1

1/2/2019

Jan 2019

2

Sun

12/28/2018

1/2/2019

1/4/2019

1

2018/52

201801

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

1/3/2019

1/4/2019

1

53

2018/52

201853

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

12/6/2018

1/4/2019

1

53

2018/52

201853

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

12/3/2018

1/4/2019

1

53

2018/52

201853

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

1/3/2019

1/4/2019

1

2018/52

201801

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

12/6/2018

1/4/2019

1

2018/52

201801

1/31/2019

Q1

1/3/2019

Jan 2019

3

Mon

12/28/2018

12/3/2018

1/4/2019

1

2018/52

201801

1/31/2019

Q1

1/4/2019

Jan 2019

4

Tue

1/4/2019

1/11/2019

1

2

2018/52

201902

1/31/2019

Q1

1/5/2019

Jan 2019

5

Wed

1/4/2019

1/5/2019

1/11/2019

1

2

2018/52

201902

1/31/2019

Q1

Where I should change my script? Please help, I’m frustrated with my little knowledge in scripting and practicing trial and error.

I sincerely appreciate your kind help. Thank you very much!

Best regards,

Jami

JustinDallas · ‎2019-02-06

Just a few tips, while specificity of your problem is always welcome, your post comes off as a wall of text and that will scare folks off.

It appears that you need to use your WeekEnd date to determine things such as Week number and Year. I've got something that looks like this:

Here is the script where I accomplished (or didn't accomplish) this. If you notice, I make things directly or indirectly dependent on the shifted WeekEnd and WeekStart.

LET vStartDate = Num(MonthStart('12/01/2018'))
;
LET vEndDate = Num(MonthEnd('01/01/2019'))
;

MasterCalendar:
LOAD *,
 Year(WeekEnd(CalendarDate, 0 , 4)) & '/' & [ShimWeekNumber] AS 'SP_Week_No' 
;
Load 
 TempDate AS '%calendar_date_key',
 TempDate As CalendarDate,
 WeekStart(TempDate, 0 , 4 ) AS 'ShiftedWeekStart',
 Week(WeekEnd(TempDate, 0 , 4)) AS 'ShimWeekNumber',
 Num(Month(TempDate)) AS 'MonthNum',
 Day(TempDate) As Day ,
 WeekDay(TempDate) as WeekDay
;
LOAD 
 Date($(vStartDate) + IterNo()) AS TempDate
 AutoGenerate 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

MasterCal2:
NoConcatenate
LOAD * Resident MasterCalendar WHERE MonthNum = 12 OR MonthNum = 1
;
DROP TABLE MasterCalendar
;

Let me know if this helped or if it didn't.

mja_jami · ‎2019-02-06

Hi Justin,

Thank you very much for your help and not getting scared of my wall of text. It lines up with my 2017 & 2018 data nicely only that when I checked 2016 dates, I see SP_Week_No started from 2 than 1 and therefore it shows different than my calendar:

week#	Week starts	Week ends
	12/25/2015	12/31/2015
1	1/1/2016	1/7/2016
2	1/8/2016	1/14/2016
51	12/16/2016	12/22/2016
52	12/23/2016	12/29/2016
1	12/30/2016	1/5/2017

ShiftedWeekStart	production_date	SP_Week_No	ShimWeekNumber
1/1/2016	1/7/2016	2016/2	2
1/8/2016	1/14/2016	2016/3	3
12/16/2016	12/22/2016	2016/52	52
12/23/2016	12/29/2016	2016/53	53
12/30/2016	1/5/2017	2017/1	1

The other trouble is WeekDay is not the real date for the date. However, it’s already way better than before, all I am hoping you could find why 2016 week# is not matching and started from 2.

Thank you very much for your help. Highly appreciated.

JustinDallas · ‎2019-02-06

Are you sure you didn't change anything? This is what I get.

LET vStartDate = Num(MonthStart('12/01/2015'))
;
LET vEndDate = Num(MonthEnd('01/01/2017'))
;

MasterCalendar:
LOAD *,
 Year(WeekEnd(CalendarDate, 0 , 4)) & '/' & [ShimWeekNumber] AS 'SP_Week_No' 
;
Load 
 TempDate AS '%calendar_date_key',
 TempDate As CalendarDate,
 WeekStart(TempDate, 0 , 4 ) AS 'ShiftedWeekStart',
 Week(WeekEnd(TempDate, 0 , 4)) AS 'ShimWeekNumber',
 Num(Month(TempDate)) AS 'MonthNum',
 Day(TempDate) As Day ,
 WeekDay(TempDate) as WeekDay
;
LOAD 
 Date($(vStartDate) + IterNo()) AS TempDate
 AutoGenerate 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;

MasterCal2:
NoConcatenate
LOAD * Resident MasterCalendar WHERE MonthNum = 12 OR MonthNum = 1
;
DROP TABLE MasterCalendar
;

mja_jami · ‎2019-02-06

Hi Justin,

I didn’t change anything, pasted your latest script again and see 1/1/2016 started with ShimWeekNumber 2.

And I didn’t connect to any other tables through any common field but want to. I’ve a link table which selects the date type (production/ effective/ cancel) and want to connect to this MasterCal2 with the key %Date replacing %calendar_date.

Thank you very much for your time, patience and effort.

customized calendar