This function returns a display value showing the year and lunar week number corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1as the first day of the week and, apart from the final week of the year, will contain exactly seven days.
Syntax:
LunarWeekName(date [, period_no[, first_week_day]])
Return data type: dual
Example diagram of lunarweekname() function
The lunarweekname() function determines which lunar week the date falls into, beginning a week count from January 1. It then returns a value comprised of year/weekcount.
| Argument | Description |
|---|---|
| date | The date or timestamp to evaluate. |
| period_no | period_no is an integer or expression resolving to an integer, where the value 0 indicates the lunar week which contains date. Negative values in period_no indicate preceding lunar weeks and positive values indicate succeeding lunar weeks. |
| first_week_day | An offset that may be greater than or less than zero. This changes the beginning of the year by the specified number of days and/or fractions of a day. |
When to use it
The lunarweekname() function is useful when you would like to compare aggregations by lunar weeks. For example, the function could be used to determine the total sales of products by lunar week. Lunar weeks are useful when you would like to ensure that all values contained in the first week of the year contain only values from January 1 at the earliest.
These dimensions can be created in the load script by using the function to create a field in a Master Calendar table. The function can also be used directly in a chart as a calculated dimension.
| Example | Result |
|---|---|
| lunarweekname('01/12/2013') | Returns 2006/02. |
| lunarweekname('01/12/2013', -1) | Returns 2006/01. |
| lunarweekname('01/12/2013', 0, 1) | Returns 2006/02. |
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 – date with no additional arguments
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 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, lunar_week_name, that returns the year and week number for the lunar week in which the transactions took place.
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
lunarweekname(date) as lunar_week_name
;
Load
*
Inline
[
id,date,amount
8188,1/7/2022,17.17
8189,1/19/2022,37.23
8190,2/28/2022,88.27
8191,2/5/2022,57.42
8192,3/16/2022,53.80
8193,4/1/2022,82.06
8194,5/7/2022,40.39
8195,5/16/2022,87.21
8196,6/15/2022,95.93
8197,6/26/2022,45.89
8198,7/9/2022,36.23
8199,7/22/2022,25.66
8200,7/23/2022,82.77
8201,7/27/2022,69.98
8202,8/2/2022,76.11
8203,8/8/2022,25.12
8204,8/19/2022,46.23
8205,9/26/2022,84.21
8206,10/14/2022,96.24
8207,10/29/2022,67.67
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
date
-
lunar_week_name
| date | lunar_week_name |
|---|---|
| 1/7/2022 | 2022/01 |
| 1/19/2022 | 2022/03 |
| 2/5/2022 | 2022/06 |
| 2/28/2022 | 2022/09 |
| 3/16/2022 | 2022/11 |
| 4/1/2022 | 2022/13 |
| 5/7/2022 | 2022/19 |
| 5/16/2022 | 2022/20 |
| 6/15/2022 | 2022/24 |
| 6/26/2022 | 2022/26 |
| 7/9/2022 | 2022/28 |
| 7/22/2022 | 2022/29 |
| 7/23/2022 | 2022/30 |
| 7/27/2022 | 2022/30 |
| 8/2/2022 | 2022/31 |
| 8/8/2022 | 2022/32 |
| 8/19/2022 | 2022/33 |
| 9/26/2022 | 2022/39 |
| 10/14/2022 | 2022/41 |
| 10/29/2022 | 2022/44 |
The lunar_week_name field is created in the preceding load statement by using the lunarweekname() function, and passing the date field as the function’s argument.
The lunarweekname() function identifies which lunar week the date value falls into, returning the year and week number of that date.
Diagram of lunarweekname() function, example with no additional arguments
Transaction 8189 took place on January 19. The lunarweekname() function identifies that this date falls into the lunar week beginning on January 15; this is the third lunar week of the year. Therefore, the lunar_week_name value returned for that transaction is 2022/03.
Example 2 – date with period_no argument
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.
-
The creation of a field, previous_lunar_week_name, that returns the year and week number for the lunar week prior to when the transactions took place.
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
lunarweekname(date,-1) as previous_lunar_week_name
;
Load
*
Inline
[
id,date,amount
8188,1/7/2022,17.17
8189,1/19/2022,37.23
8190,2/28/2022,88.27
8191,2/5/2022,57.42
8192,3/16/2022,53.80
8193,4/1/2022,82.06
8194,5/7/2022,40.39
8195,5/16/2022,87.21
8196,6/15/2022,95.93
8197,6/26/2022,45.89
8198,7/9/2022,36.23
8199,7/22/2022,25.66
8200,7/23/2022,82.77
8201,7/27/2022,69.98
8202,8/2/2022,76.11
8203,8/8/2022,25.12
8204,8/19/2022,46.23
8205,9/26/2022,84.21
8206,10/14/2022,96.24
8207,10/29/2022,67.67
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
date
-
previous_lunar_week_name
| date | previous_lunar_week_name |
|---|---|
| 1/7/2022 | 2021/52 |
| 1/19/2022 | 2022/02 |
| 2/5/2022 | 2022/05 |
| 2/28/2022 | 2022/08 |
| 3/16/2022 | 2022/10 |
| 4/1/2022 | 2022/12 |
| 5/7/2022 | 2022/18 |
| 5/16/2022 | 2022/19 |
| 6/15/2022 | 2022/23 |
| 6/26/2022 | 2022/25 |
| 7/9/2022 | 2022/27 |
| 7/22/2022 | 2022/28 |
| 7/23/2022 | 2022/29 |
| 7/27/2022 | 2022/29 |
| 8/2/2022 | 2022/30 |
| 8/8/2022 | 2022/31 |
| 8/19/2022 | 2022/32 |
| 9/26/2022 | 2022/38 |
| 10/14/2022 | 2022/40 |
| 10/29/2022 | 2022/43 |
In this instance, because a period_no of -1 was used as the offset argument in the lunarweekname() function, the function first identifies the lunar week in which the transactions took place. It then returns the year and the number of one week prior.
Diagram of lunarweekname() function, period_no example
Transaction 8189 took place on January 19. The lunarweekname() function identifies that this transaction took place in the third lunar week of the year, so it then returns the year and value for one week prior, 2022/02, for the previous_lunar_week_name field.
Example 3 – date with first_week_day argument
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 set lunar weeks to begin on January 5.
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
lunarweekname(date,0,4) as lunar_week_name
;
Load
*
Inline
[
id,date,amount
8188,1/7/2022,17.17
8189,1/19/2022,37.23
8190,2/28/2022,88.27
8191,2/5/2022,57.42
8192,3/16/2022,53.80
8193,4/1/2022,82.06
8194,5/7/2022,40.39
8195,5/16/2022,87.21
8196,6/15/2022,95.93
8197,6/26/2022,45.89
8198,7/9/2022,36.23
8199,7/22/2022,25.66
8200,7/23/2022,82.77
8201,7/27/2022,69.98
8202,8/2/2022,76.11
8203,8/8/2022,25.12
8204,8/19/2022,46.23
8205,9/26/2022,84.21
8206,10/14/2022,96.24
8207,10/29/2022,67.67
];Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
date
-
lunar_week_name
| date | lunar_week_name |
|---|---|
| 1/7/2022 | 2022/01 |
| 1/19/2022 | 2022/03 |
| 2/5/2022 | 2022/05 |
| 2/28/2022 | 2022/08 |
| 3/16/2022 | 2022/11 |
| 4/1/2022 | 2022/13 |
| 5/7/2022 | 2022/18 |
| 5/16/2022 | 2022/19 |
| 6/15/2022 | 2022/24 |
| 6/26/2022 | 2022/25 |
| 7/9/2022 | 2022/27 |
| 7/22/2022 | 2022/29 |
| 7/23/2022 | 2022/29 |
| 7/27/2022 | 2022/30 |
| 8/2/2022 | 2022/30 |
| 8/8/2022 | 2022/31 |
| 8/19/2022 | 2022/33 |
| 9/26/2022 | 2022/38 |
| 10/14/2022 | 2022/41 |
| 10/29/2022 | 2022/43 |
Diagram of lunarweekname() function, first_week_day example
In this instance, because the first_week_date argument of 4 is used in the lunarweekname() function, it offsets the start of lunar weeks from January 1 to January 5.
Transaction 8188 took place on January 7. Due to lunar weeks beginning on January 5, the lunarweekname() function identifies that the lunar week containing January 7 is the first lunar week of the year. Therefore, the returned lunar_week_name value for that transaction is 2022/01.
Example 4 – 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 lunar week number and year in which the transactions took place is created as a measure in a chart object of the application.
Load script
Transactions:
Load
*
Inline
[
id,date,amount
8188,1/7/2022,17.17
8189,1/19/2022,37.23
8190,2/28/2022,88.27
8191,2/5/2022,57.42
8192,3/16/2022,53.80
8193,4/1/2022,82.06
8194,5/7/2022,40.39
8195,5/16/2022,87.21
8196,6/15/2022,95.93
8197,6/26/2022,45.89
8198,7/9/2022,36.23
8199,7/22/2022,25.66
8200,7/23/2022,82.77
8201,7/27/2022,69.98
8202,8/2/2022,76.11
8203,8/8/2022,25.12
8204,8/19/2022,46.23
8205,9/26/2022,84.21
8206,10/14/2022,96.24
8207,10/29/2022,67.67
];Results
Load the data and open a sheet. Create a new table and add this field as a dimension: date.
To calculate the start date of the lunar week in which a transaction takes place, create the following measure:
=lunarweekname(date)
| date | =lunarweekname(date) |
|---|---|
| 1/7/2022 | 2022/01 |
| 1/19/2022 | 2022/03 |
| 2/5/2022 | 2022/06 |
| 2/28/2022 | 2022/09 |
| 3/16/2022 | 2022/11 |
| 4/1/2022 | 2022/13 |
| 5/7/2022 | 2022/19 |
| 5/16/2022 | 2022/20 |
| 6/15/2022 | 2022/24 |
| 6/26/2022 | 2022/26 |
| 7/9/2022 | 2022/28 |
| 7/22/2022 | 2022/29 |
| 7/23/2022 | 2022/30 |
| 7/27/2022 | 2022/30 |
| 8/2/2022 | 2022/31 |
| 8/8/2022 | 2022/32 |
| 8/19/2022 | 2022/33 |
| 9/26/2022 | 2022/39 |
| 10/14/2022 | 2022/41 |
| 10/29/2022 | 2022/44 |
The lunar_week_name measure is created in the chart object by using the lunarweekname() function and passing the date field as the function’s argument.
The lunarweekname() function identifies which lunar week the date value falls into, returning the year and week number of that date.
Diagram of lunarweekname() function, chart object example
Transaction 8189 took place on January 19. The lunarweekname() function identifies that this date falls into the lunar week beginning on January 15; this is the third lunar week of the year. Therefore, the lunar_week_name value for that transaction is 2022/03.