Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
mja_jami
New Contributor III

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

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 Temp53wks.png

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

Labels (3)
5 Replies
GillesB
New Contributor

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

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

Thanks

Partner
Partner

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

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...
Please ekskuse my Norglish and Swenglish typos.
mja_jami
New Contributor III

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

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

Partner
Partner

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

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
;

image.png

 

Please ekskuse my Norglish and Swenglish typos.
mja_jami
New Contributor III

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

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,

53 weeks again.PNG

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?

stopped.PNG

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

main setting.PNG

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