week - script and chart function

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]]])

Diagram showing how the week function breaks up dates within the first month of the year, applying the default broken weeks system. — 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.

Diagram showing how the week function breaks up dates within the first month of the year, without applying the broken weeks system. In this example, the ReferenceDay parameter is also given a value of 0. — 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.

FirstWeekDay

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)

BrokenWeeks

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)

ReferenceDay

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.

Function examples
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

Results table
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 displaying how the week function breaks up the dates of year into corresponding week numbers. Here, default system variables lead week 1 to begin on January 1, with week 2 starting on January 2. — 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

Results table
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 displaying how the week function breaks up the dates of year into corresponding week numbers. Here, with weeks beginning on Jaunary 2, week 1 of the year starts on January 1 and week 2 starts on January 4. — 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

ADD ALT TEXT HERE.....!!!!!! — Diagram of week() function, chart object example

Results table
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 displaying how the week function breaks up the dates of year into corresponding week numbers. Here, since we use unbroken weeks, week 1 of the year starts on January 2, a Sunday. — 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

Results table
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

Diagram displaying how the week function breaks up the dates of year into corresponding week numbers. Here, week 1 begins on December 28, 2021, and concludes on Monday, January 3, 2022. — 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)

Results table
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 displaying how the week function breaks up the dates of year into corresponding week numbers. Here, week 1 begins on January 1, with week 2 starting on January 2. — 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.

Example 6 – Scenario

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 2019 and first two weeks of 2020, which is loaded into a table called Transactions.
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.

The application primarily uses broken weeks across its dashboard. However, the end user would like a chart object that presents the total sales by week using unbroken weeks. The reference day should be January 2, with weeks beginning on a Tuesday. This could be achieved even when this dimension is not available in the data model, using the week() function as a calculated dimension in the chart.

Load script

SET BrokenWeeks=1;
SET ReferenceDay=0;
SET DateFormat='MM/DD/YYYY';

Transactions:
Load
*
Inline 
[
id,date,amount
8183,12/27/2019,58.27
8184,12/28/2019,67.42
8185,12/29/2019,23.80
8186,12/30/2019,82.06
8187,12/31/2019,40.56
8188,01/01/2020,37.23
8189,01/02/2020,17.17
8190,01/03/2020,88.27
8191,01/04/2020,57.42
8192,01/05/2020,53.80
8193,01/06/2020,82.06
8194,01/07/2020,40.56
8195,01/08/2020,53.67
8196,01/09/2020,26.63
8197,01/10/2020,72.48
8198,01/11/2020,18.37
8199,01/12/2020,45.26
8200,01/13/2020,58.23
8201,01/14/2020,18.52
];

Results

Do the following:

Load the data and open a sheet. Create a new table.
Create the following calculated dimension:

=week(date)
Next, create the following aggregation measure:

=sum(amount)
Set the measure's Number formatting to Money.
Select the Sorting menu, and for the calculated dimension, remove custom sorting.
De-select the Sort numerically and Sort alphabetically options.

Results table
week(date)	sum(amount)
52	$125.69
53	$146.42
1	$200.09
2	$347.57
3	$122.01

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here