This function returns an integer representing the week number according to ISO 8601. The week number is calculated from the date interpretation of the expression, according to the standard number interpretation.
Syntax:
week(timestamp [, first_week_day [, broken_weeks [, reference_day]]])
Example diagram of week() function, with broken weeks
The week number count begins on January 1 (this is because Qlik Sense is set by default to use broken weeks). The first week ends on the day prior to the FirstWeekDay system variable regardless of how many days have occurred in that week. The FirstWeekDay system variable can be superseded within the week() function by the first_week_day argument.
Example diagram of week() function, with unbroken weeks and ReferenceDay=0
The week() function also provides the ability to specify whether to use broken or unbroken weeks via the broken_weeks argument. If the broken week functionality is employed, week 1 must contain a certain number of days in January as defined by the ReferenceDay system variable. Therefore, week 1 can potentially begin in December, or alternatively, weeks 52 or 53 may continue into January. Finally, the reference_day argument allows the function to override the ReferenceDay system variable.
Unlike the weekname() function, the week() function does not return the year value as well. This allows for aggregations comparing weeks across years.
There are four arguments that can be used within this function.
Argument # 1: timestamp
This is the date to evaluate as a timestamp or expression resolving to a timestamp, to convert, for example '2012-10-12'.
Argument # 2: first_week_day
If you don't specify first_week_day, the value of variable FirstWeekDay will be used as the first day of the week.
If you want to use another day as the first day of the week, set first_week_day to:
- 0 for Monday
- 1 for Tuesday
- 2 for Wednesday
- 3 for Thursday
- 4 for Friday
- 5 for Saturday
- 6 for Sunday
The integer returned by the function will now use the first day of the week that you set with first_week_day.
Argument # 3: broken_weeks
If you don't specify broken_weeks, the value of variable BrokenWeeks will be used to define if weeks are broken or not.
By default Qlik Sense functions use unbroken weeks. This means that:
- In some years, week 1 starts in December, and in other years, week 52 or 53 continues into January.
- Week 1 always has at least 4 days in January.
The alternative is to use broken weeks.
- Week 52 or 53 do not continue into January.
- Week 1 starts on January 1 and is, in most cases, not a full week.
The following values can be used:
- 0 (=use unbroken weeks)
- 1 (= use broken weeks)
Argument # 4: reference_day
If you don't specify reference_day, the value of variable ReferenceDay will be used to define which day in January to set as reference day to define week 1. By default, Qlik Sense functions use 4 as the reference day. This means that week 1 must contain January 4, or put differently, that week 1 must always have at least 4 days in January.
The following values can be used to set a different reference day:
- 1 (= January 1)
- 2 (= January 2)
- 3 (= January 3)
- 4 (= January 4)
- 5 (= January 5)
- 6 (= January 6)
- 7 (= January 7)
When to use it
The week() function is useful when you would like to compare aggregations by weeks. For example, it could be used if you would like to see the total sales of products by week. The week() function is chosen over weekname() when the user would like the calculation to not necessarily use the application’s BrokenWeeks, FirstWeekDay, or ReferenceDay system variables.
Additionally, the week() function is chosen when you would like to compare across multiple years. By using the week() function, the user is able to create their own combination of these variables to be used in instances when the function is used.
These dimensions can be created either in the load script by using the function to create a field in a Master Calendar table, or used directly in a chart as a calculated dimension.
| Example | Result |
|---|---|
|
week( '10/12/2012') |
Returns 41. |
| week( '35648') | Returns 32, because 35648 = 08/06/1997. |
| week('10/12/2012', 0, 1) | Returns 42. |
Regional settings
Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples.
Default regional settings in apps are based on the regional system settings of the computer or server where Qlik Sense is installed. If the Qlik Sense server you are accessing is set to Sweden, the Data load editor will use Swedish regional settings for dates, time, and currency. These regional format settings are not related to the language displayed in the Qlik Sense user interface. Qlik Sense will be displayed in the same language as the browser you are using.
Example 1 – Default system variables
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
-
A dataset containing a set of transactions for the last week of 2021 and the first two weeks of 2022, which is loaded into a table called Transactions.
-
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.
-
The creation of a field, week_number, that returns the year and week number when the transactions took place.
-
The creation of a field called week_day, showing the weekday value of each transaction date.
Load script
SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
Transactions:
Load
*,
WeekDay(date) as week_day,
Week(date) as week_number
;
Load
*
Inline
[
id,date,amount
8183,12/27/2021,58.27
8184,12/28/2021,67.42
8185,12/29/2021,23.80
8186,12/30/2021,82.06
8187,12/31/2021,40.56
8188,01/01/2022,37.23
8189,01/02/2022,17.17
8190,01/03/2022,88.27
8191,01/04/2022,57.42
8192,01/05/2022,53.80
8193,01/06/2022,82.06
8194,01/07/2022,40.56
8195,01/08/2022,53.67
8196,01/09/2022,26.63
8197,01/10/2022,72.48
8198,01/11/2022,18.37
8199,01/12/2022,45.26
8200,01/13/2022,58.23
8201,01/14/2022,18.52
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
id
-
date
-
week_day
-
week_number
| id | date | week_day | week_number |
|---|---|---|---|
| 8183 | 12/27/2021 | Mon | 53 |
| 8184 | 12/28/2021 | Tue | 53 |
| 8185 | 12/29/2021 | Wed | 53 |
| 8186 | 12/30/2021 | Thu | 53 |
| 8187 | 12/31/2021 | Fri | 53 |
| 8188 | 01/01/2022 | Sat | 1 |
| 8189 | 01/02/2022 | Sun | 2 |
| 8190 | 01/03/2022 | Mon | 2 |
| 8191 | 01/04/2022 | Tue | 2 |
| 8192 | 01/05/2022 | Wed | 2 |
| 8193 | 01/06/2022 | Thu | 2 |
| 8194 | 01/07/2022 | Fri | 2 |
| 8195 | 01/08/2022 | Sat | 2 |
| 8196 | 01/09/2022 | Sun | 3 |
| 8197 | 01/10/2022 | Mon | 3 |
| 8198 | 01/11/2022 | Tue | 3 |
| 8199 | 01/12/2022 | Wed | 3 |
| 8200 | 01/13/2022 | Thu | 3 |
| 8201 | 01/14/2022 | Fri | 3 |
The week_number field is created in the preceding load statement by using the week() function and passing the date field as the function’s argument.
No other parameters are passed into the function, and therefore the following default variables that affect the week() function are in effect:
-
BrokenWeeks: The week count begins on January 1
-
FirstWeekDay: The first day of the week is Sunday
Diagram of week() function, using default system variables
Because the application is using the default BrokenWeeks system variable, week 1 begins on January 1, a Saturday.
Because of the default FirstWeekDay system variable, weeks begin on a Sunday. The first Sunday after January 1 occurs on January 2, which is when week 2 begins.
Example 2 – first_week_day
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
-
The creation of a field, week_number, that returns the year and week number when the transactions took place.
-
The creation of a field called week_day, showing the weekday value of each transaction date.
In this example, we would like to set the start of the work week to Tuesday.
Load script
SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
Transactions:
Load
*,
WeekDay(date) as week_day,
Week(date,1) as week_number
;
Load
*
Inline
[
id,date,amount
8183,12/27/2022,58.27
8184,12/28/2022,67.42
8185,12/29/2022,23.80
8186,12/30/2022,82.06
8187,12/31/2021,40.56
8188,01/01/2022,37.23
8189,01/02/2022,17.17
8190,01/03/2022,88.27
8191,01/04/2022,57.42
8192,01/05/2022,53.80
8193,01/06/2022,82.06
8194,01/07/2022,40.56
8195,01/08/2022,53.67
8196,01/09/2022,26.63
8197,01/10/2022,72.48
8198,01/11/2022,18.37
8199,01/12/2022,45.26
8200,01/13/2022,58.23
8201,01/14/2022,18.52
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
id
-
date
-
week_day
-
week_number
| id | date | week_day | week_number |
|---|---|---|---|
| 8183 | 12/27/2021 | Mon | 52 |
| 8184 | 12/28/2021 | Tue | 53 |
| 8185 | 12/29/2021 | Wed | 53 |
| 8186 | 12/30/2021 | Thu | 53 |
| 8187 | 12/31/2021 | Fri | 53 |
| 8188 | 01/01/2022 | Sat | 1 |
| 8189 | 01/02/2022 | Sun | 1 |
| 8190 | 01/03/2022 | Mon | 1 |
| 8191 | 01/04/2022 | Tue | 2 |
| 8192 | 01/05/2022 | Wed | 2 |
| 8193 | 01/06/2022 | Thu | 2 |
| 8194 | 01/07/2022 | Fri | 2 |
| 8195 | 01/08/2022 | Sat | 2 |
| 8196 | 01/09/2022 | Sun | 2 |
| 8197 | 01/10/2022 | Mon | 2 |
| 8198 | 01/11/2022 | Tue | 3 |
| 8199 | 01/12/2022 | Wed | 3 |
| 8200 | 01/13/2022 | Thu | 3 |
| 8201 | 01/14/2022 | Fri | 3 |
The application is still using broken weeks. However, the first_week_day argument has been set to 1 in the week() function. This sets the first day of the week to a Tuesday.
Diagram of week() function, first_week_day example
The application is using the default BrokenWeeks system variable, so week 1 begins on January 1, a Saturday.
The first_week_day argument of the week() function sets the first week day to a Tuesday. Therefore, week 53 begins on December 28, 2021.
However, because the function is still using broken weeks, week 1 will only be two days long, due to the first Tuesday after January 1 occurring on January 3.
Example 3 – unbroken_weeks
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains the same dataset and scenario as the first example.
In this example, we use unbroken weeks.
Load script
SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
Transactions:
Load
*,
WeekDay(date) as week_day,
Week(date,6,0) as week_number
;
Load
*
Inline
[
id,date,amount
8183,12/27/2022,58.27
8184,12/28/2022,67.42
8185,12/29/2022,23.80
8186,12/30/2022,82.06
8187,12/31/2021,40.56
8188,01/01/2022,37.23
8189,01/02/2022,17.17
8190,01/03/2022,88.27
8191,01/04/2022,57.42
8192,01/05/2022,53.80
8193,01/06/2022,82.06
8194,01/07/2022,40.56
8195,01/08/2022,53.67
8196,01/09/2022,26.63
8197,01/10/2022,72.48
8198,01/11/2022,18.37
8199,01/12/2022,45.26
8200,01/13/2022,58.23
8201,01/14/2022,18.52
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
id
-
date
-
week_day
-
week_number
Diagram of week() function, chart object example
| id | date | week_day | week_number |
|---|---|---|---|
| 8183 | 12/27/2021 | Mon | 52 |
| 8184 | 12/28/2021 | Tue | 52 |
| 8185 | 12/29/2021 | Wed | 52 |
| 8186 | 12/30/2021 | Thu | 52 |
| 8187 | 12/31/2021 | Fri | 52 |
| 8188 | 01/01/2022 | Sat | 52 |
| 8189 | 01/02/2022 | Sun | 1 |
| 8190 | 01/03/2022 | Mon | 1 |
| 8191 | 01/04/2022 | Tue | 1 |
| 8192 | 01/05/2022 | Wed | 1 |
| 8193 | 01/06/2022 | Thu | 1 |
| 8194 | 01/07/2022 | Fri | 1 |
| 8195 | 01/08/2022 | Sat | 1 |
| 8196 | 01/09/2022 | Sun | 2 |
| 8197 | 01/10/2022 | Mon | 2 |
| 8198 | 01/11/2022 | Tue | 2 |
| 8199 | 01/12/2022 | Wed | 2 |
| 8200 | 01/13/2022 | Thu | 2 |
| 8201 | 01/14/2022 | Fri | 2 |
The first_week_date parameter is set to 1, making Tuesday the first day of the week. The broken_weeks parameter it set to 0, forcing the function to use unbroken weeks. Finally, the third parameter sets the reference_day to 2.
The first_week_date parameter is set to 6, making Sunday the first day of the week. The broken_weeks parameter is set to 0, forcing the function to use unbroken weeks.
Diagram of week() function, example using unbroken weeks
By using unbroken weeks, week 1 does not necessarily begin on January 1; instead, it is required to have a minimum of four days. Therefore, in the dataset, week 52 concludes on Saturday, January 1, 2022. Week 1 then begins on the FirstWeekDay system variable, which is Sunday, January 2. This week will conclude on the following Saturday, January 8.
Example 4 – reference_day
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
-
The same dataset and scenario as the third example.
-
The creation of a field, week_number, that returns the year and week number when the transactions took place.
-
The creation of a field called week_day, showing the weekday value of each transaction date.
Additionally, the following conditions must be met:
-
The work week begins on a Tuesday.
-
The company uses unbroken weeks.
-
The reference_day value is 2. In other words, the minimum number of days in January in week 1 will be 2.
Load script
SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
Transactions:
Load
*,
WeekDay(date) as week_day,
Week(date,1,0,2) as week_number
;
Load
*
Inline
[
id,date,amount
8183,12/27/2022,58.27
8184,12/28/2022,67.42
8185,12/29/2022,23.80
8186,12/30/2022,82.06
8187,12/31/2021,40.56
8188,01/01/2022,37.23
8189,01/02/2022,17.17
8190,01/03/2022,88.27
8191,01/04/2022,57.42
8192,01/05/2022,53.80
8193,01/06/2022,82.06
8194,01/07/2022,40.56
8195,01/08/2022,53.67
8196,01/09/2022,26.63
8197,01/10/2022,72.48
8198,01/11/2022,18.37
8199,01/12/2022,45.26
8200,01/13/2022,58.23
8201,01/14/2022,18.52
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
id
-
date
-
week_day
-
week_number
| id | date | week_day | week_number |
|---|---|---|---|
| 8183 | 12/27/2021 | Mon | 52 |
| 8184 | 12/28/2021 | Tue | 1 |
| 8185 | 12/29/2021 | Wed | 1 |
| 8186 | 12/30/2021 | Thu | 1 |
| 8187 | 12/31/2021 | Fri | 1 |
| 8188 | 01/01/2022 | Sat | 1 |
| 8189 | 01/02/2022 | Sun | 1 |
| 8190 | 01/03/2022 | Mon | 1 |
| 8191 | 01/04/2022 | Tue | 2 |
| 8192 | 01/05/2022 | Wed | 2 |
| 8193 | 01/06/2022 | Thu | 2 |
| 8194 | 01/07/2022 | Fri | 2 |
| 8195 | 01/08/2022 | Sat | 2 |
| 8196 | 01/09/2022 | Sun | 2 |
| 8197 | 01/10/2022 | Mon | 2 |
| 8198 | 01/11/2022 | Tue | 3 |
| 8199 | 01/12/2022 | Wed | 3 |
| 8200 | 01/13/2022 | Thu | 3 |
| 8201 | 01/14/2022 | Fri | 3 |
The first_week_date parameter is set to 1, making Tuesday the first day of the week. The broken_weeks parameter it set to 0, forcing the function to use unbroken weeks. Finally, the third parameter sets the reference_day parameter to 2.
Diagram of week() function, reference_day example
With the function using unbroken weeks and a reference_day value of 2 used as a parameter, week 1 only needs to include two days in January. Due to the first weekday being Tuesday, week 1 begins on December 28, 2021, and concludes on Monday, January 3, 2022.
Example 5 – Chart object example
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains the same dataset and scenario as the first example.
However, in this example, the unchanged dataset is loaded into the application. The calculation that returns the week number is created as a measure in a chart object.
Load script
Transactions:
Load
*
Inline
[
id,date,amount
8183,12/27/2022,58.27
8184,12/28/2022,67.42
8185,12/29/2022,23.80
8186,12/30/2022,82.06
8187,12/31/2021,40.56
8188,01/01/2022,37.23
8189,01/02/2022,17.17
8190,01/03/2022,88.27
8191,01/04/2022,57.42
8192,01/05/2022,53.80
8193,01/06/2022,82.06
8194,01/07/2022,40.56
8195,01/08/2022,53.67
8196,01/09/2022,26.63
8197,01/10/2022,72.48
8198,01/11/2022,18.37
8199,01/12/2022,45.26
8200,01/13/2022,58.23
8201,01/14/2022,18.52
];Results
Do the following:
-
Load the data and open a sheet. Create a new table.
-
Add the following fields as dimensions:
-
id
-
date
-
-
Next, create the following measure:
=week (date)
-
Create a measure, week_day, to show the weekday value of each transaction date:
=weekday(date)
| id | date | =week(date) | =weekday(date) |
|---|---|---|---|
| 8183 | 12/27/2021 | 53 | Mon |
| 8184 | 12/28/2021 | 53 | Tue |
| 8185 | 12/29/2021 | 53 | Wed |
| 8186 | 12/30/2021 | 53 | Thu |
| 8187 | 12/31/2021 | 53 | Fri |
| 8188 | 01/01/2022 | 1 | Sat |
| 8189 | 01/02/2022 | 2 | Sun |
| 8190 | 01/03/2022 | 2 | Mon |
| 8191 | 01/04/2022 | 2 | Tue |
| 8192 | 01/05/2022 | 2 | Wed |
| 8193 | 01/06/2022 | 2 | Thu |
| 8194 | 01/07/2022 | 2 | Fri |
| 8195 | 01/08/2022 | 2 | Sat |
| 8196 | 01/09/2022 | 3 | Sun |
| 8197 | 01/10/2022 | 3 | Mon |
| 8198 | 01/11/2022 | 3 | Tue |
| 8199 | 01/12/2022 | 3 | Wed |
| 8200 | 01/13/2022 | 3 | Thu |
| 8201 | 01/14/2022 | 3 | Fri |
The week_number field is created in the preceding load statement by using the week() function and passing the date field as the function’s argument.
No other parameters are passed into the function, and therefore the following default variables that affect the week() function are in effect:
-
BrokenWeeks: The week count begins on January 1
-
FirstWeekDay: The first day of the week is Sunday
Diagram of week() function, chart object example
Because the application is using the default BrokenWeeks system variable, week 1 begins on January 1, a Saturday.
Because of the default FirstWeekDay system variable, weeks begin on a Sunday. The first Sunday after January 1 occurs on January 2, which is when week 2 begins.