Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | 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 | 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 | 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 | 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 | 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 | 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 | 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 | 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/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 | 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 | 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 | 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 | 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 | 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 | 1 | 2018/52 | 201801 | 1/31/2019 | Q1 |
1/4/2019 | Jan 2019 | 4 | Tue | 1/4/2019 | 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
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.
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.
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
;
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.
