Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lwp89
Contributor III
Contributor III

Start week from Monday in the Autocalendar script

How to get the week start from Monday?

 

When loading data using the Data Manager, the script generates an auto-calendar script where it creates very useful fields such as Week Number, Preivous week tag, etc,. (below is the script sample)

 

[autoCalendar]: 
  DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
  Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
  Month($1) AS [Month] Tagged ('$month', '$cyclic'),
  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
  Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
  Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
  Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
  If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
  Year(Today())-Year($1) AS [YearsAgo] ,
  If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
  4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
  Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
  If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
  12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
  Month(Today())-Month($1) AS [MonthRelNo] ,
  If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
  (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
  Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [Release Date] USING [autoCalendar] ;

 

The date field name is [Release Date] and the above script produces below additional fields

lwp89_1-1664057846653.png

How do I make the Sunday (09-18-2022) included in previous week instead of this week?

  • So the weeksAgo value for that date (09-18-2022) becomes 1 instead of 0?
  • and the weeksAgo value for that date (09-11-2022) becomes 2 instead of 1?

 

basically a week should be Monday to Sunday not Sunday to Saturday.

I hope I have explained it well 🙂

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Default Week start is defined by the system variable FirstWeekDay. It is also possible to define the week start day in the week function, like this:

week(DateField, 0)

View solution in original post

4 Replies
Vegar
MVP
MVP

Default Week start is defined by the system variable FirstWeekDay. It is also possible to define the week start day in the week function, like this:

week(DateField, 0)

lwp89
Contributor III
Contributor III
Author

Thank @Vegar I did set the FirstWeekDay = 0 and its fixed 🙂

lwp89
Contributor III
Contributor III
Author

Hi @Vegar ,

 

Just another related question.. Now that I have the correct flag for identifying dates that belongs to previous week.

 

How to find the Week start date and Week end date of the previous week? (where ReleaseDate.AutoCalendar.WeekAgo = 1

 

I looked into the WeekStartDate and WeekEndDate function but could not figure out how to best use them to find the start and end date of previous week!

 

Thanks

Vegar
MVP
MVP

  • What if you do something like this: 

WeekStart(dateField , -1 , 0)

WeekEnd(dateField , -1 , 0) 

OR dayname(WeekEnd(dateField , -1 , 0))