Calendar script returns 53 weeks for a few years than 52 for all

[mja_jami]

Dear Qlik experts,

My calendar shows 53 weeks than 52 for several years, however rest of the years’ weeks aligned fine. I’m not sure why and what can I do whether I need to change anything in my calendar scripts,

I’ve allowed SET BrokenWeeks=0; in the Main.

Temp:

        Load

        Date ($(vMinDate)+ RecNo()-1) as Date

        AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Dates:

Load Distinct

     Date as %Date ,

     Date,

     Week (Date + 2) as SPWeek2,

     weekyear(Date + 3) as SPYear2,

     Day (Date) as Day2,

WeekDay(Date) as Day,

WeekStart(Date,0,4) as WeekStart,

WeekEnd(Date,0,4)as WeekEnd

Resident Temp

Can someone help me? I sincerely appreciate your effort and kind assistance.

[GillesB]

Hi
I don't understand what you would like to do since there are years with 53 weeks and others with 52

Thanks

[Vegar]

Some years do have 53 weeks.

In Qlik Sense BrokenWeeks is set to 0 by default, meaning that week 52/53 may run into January. If you want 1th of January always to be week no 1 you should change the BrokenWeeks to 1.
https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInte...

Another approach is to use lunar weeks. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week. Read more about it in the help. https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/lun...

[mja_jami]

Thank you very much for your reply.

We follow customized calendar that has 52 weeks a year, week starts on Friday ends on Thus, e.g.;

%Date	Fiscal_Week_No	Fiscal_Week_Starts	Fiscal_Week_Ends	Fiscal_Year
12/26/2018	52	12/21/2018	12/27/2018	2018
12/27/2018	52	12/21/2018	12/27/2018	2018
12/28/2018	1	12/28/2018	1/3/2019	2019
12/20/2019	52	12/20/2019	12/26/2019	2019

I populated with excel and left joined with my Calendar but want to replace this static excl file, and generate years and weeks depending on my Min and Max dates from, however some of my script generated (WeekEnd) dates are off for some years.

[ %Date]	Day2	Day	ScriptYear	ScriptWeek2	Script_YR_Wk	ScriptWeekStart	Fiscal_Week_Starts	ScriptWeekEnd	Fiscal_Week_Ends	Fiscal_YR_Wk
12/29/2016	29	Thu	2016	52	2016/ 52	12/23/2016	12/23/2016	12/30/2016	12/29/2016	2016/ 52
12/22/2017	22	Fri	2017	52	2017/ 52	12/22/2017	12/22/2017	12/29/2017	12/28/2017	2017/ 52
12/28/2017	28	Thu	2017	52	2017/ 52	12/22/2017	12/22/2017	12/29/2017	12/28/2017	2017/ 52
12/21/2018	21	Fri	2018	52	2018/ 52	12/21/2018	12/21/2018	12/28/2018	12/27/2018	2018/ 52

 As well I get 53 weeks for some years.

[ %Date]	Day2	Day	ScriptYear	ScriptWeek2	Script_YR_Wk	ScriptWeekStart	ScriptWeekEnd
12/25/1992	25	Fri	1992	53	1992/ 53	12/25/1992	1/1/1993
12/26/1997	26	Fri	1998	53	1998/ 53	12/26/1997	1/2/1998
12/26/2003	26	Fri	2004	53	2004/ 53	12/26/2003	1/2/2004
12/26/2008	26	Fri	2009	53	2009/ 53	12/26/2008	1/2/2009
12/26/2014	26	Fri	2015	53	2015/ 53	12/26/2014	1/2/2015

Hope I could explain and look forward to your reply. 

Thank you very much.

Jami

[Vegar]

I ran your script and could not get the week 52 on the dates you describe. 

LET vMaxDate = num(AddYears(today(),-4));;
let vMinDate = num(AddYears(today(),-30));
Temp:
        Load
        Date ($(vMinDate)+ RecNo()-1) as Date
        AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Dates:
Load Distinct
     Date as %Date ,
     Date,
     Week (Date + 2) as SPWeek2,
     weekyear(Date + 3) as SPYear2,
     Day (Date) as Day2,
     WeekDay(Date) as Day,
     WeekStart(Date,0,4) as WeekStart,
     WeekEnd(Date,0,4)as WeekEnd
Resident Temp
;

[mja_jami]

Dear Vegar,

Thank you very much for your effort and time spent on it. I’ve run the script with

LET vMaxDate = num(AddYears(today(),-4));;

let vMinDate = num(AddYears(today(),-30)); and see 53 weeks again,

I’ve these stupid questions:

1. Why did you use (today(),-4 for MaxDate and (today(),-30 for MinDate?
2. Why did it stopped generating fields after 4/13/1989?

There must be something off from my side which I can’t spot. My main has this setting:

And my Calendar table:

Facts:

Load

date (policy_eff) as effective_date,

date (entry_date) as production_date

  FROM [lib://Qlik Data/Production161718191.qvd](qvd);

Tmp:

Load *, effective_date as %Date, 'Effective' as DateType

Resident Facts;

Concatenate (Tmp)

Load *,

production_date as %Date,

'Production' as DateType

Resident Facts;

Drop Table Facts;

Rename Table Tmp to Facts;

TempDates:

Load distinct date([production_date]) as %Date,

              date([production_date]) as Date

resident Facts;

Concatenate (TempDates)

Load distinct date([effective_date]) as %Date,

              date([effective_date]) as Date

resident Facts;

        NoConcatenate

        entrydatesort:

        load * resident TempDates order by %Date;

//         let vMinDate=num (Peek('%Date',0,'entrydatesort'));

//         let vMaxDate=num (Peek('%Date',-1,'entrydatesort'));

LET vMaxDate = num(AddYears(today(),-4));;

let vMinDate = num(AddYears(today(),-30));

    Temp:

        Load         Date ($(vMinDate)+ RecNo()-1) as Date

        AutoGenerate $(vMaxDate)-$(vMinDate)+1;

     Dates:

Load Distinct      Date as %Date ,

     Date,

         Week (Date + 2) as SPWeek2,

         weekyear(Date + 3) as SPYear2,

         Day (Date) as Day2,

     Year(Date) as Cal_Year,

     Monthname(Date) as [Month Year],

//      Month(Date) as Month,

     WeekStart(Date,0,4) as WeekStart,

     WeekEnd(Date,0,4)as WeekEnd,

     Num(Month(Date)) AS 'MonthNum', 

     date(floor(monthend(Date))) as MonthEndDate,

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

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

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

     inweek(Date, '$(vToday)', 0) * -1 AS CW

Resident Temp

Order by Date;

left Join(Dates)

Load min(WeekStart) as FirstWeek,

     Cal_Year

Resident Dates

Group by Cal_Year;

Left Join(Dates)

Load *,

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

Load ((WeekStart-FirstWeek)/7)  as Cal_WeekNo,

      WeekStart

Resident Dates;

drop table TempDates;

Drop table Temp;

Drop table entrydatesort;

Thank you very much for your time and effort. I sincerely appreciate your kind help.
best regards,

Jami