lunarweekname - script and chart function
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
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.
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.
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 |
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.
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.
Example 5 – 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 2022, which is loaded into a table called Transactions.
-
The date field provided in the DateFormat system variable (MM/DD/YYYY) format.
The end user would like a chart object that presents the total sales by week for the current year. Week 1, with a length of seven days, should begin on January 1. This could be achieved even when this dimension is not available in the data model by using the lunarweekname() function as a calculated dimension in the chart.
Load script
SET DateFormat='MM/DD/YYYY';
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
Do the following:
-
Load the data and open a sheet. Create a new table.
-
Create a calculated dimension using the following expression:
=lunarweekname(date)
-
Calculate total sales using the following aggregation measure:
=sum(amount)
-
Set the measure's Number formatting to Money.
=lunarweekname(date) | =sum(amount) |
---|---|
2022/01 | $17.17 |
2022/03 | $37.23 |
2022/06 | $57.42 |
2022/09 | $88.27 |
2022/11 | $53.80 |
2022/13 | $82.06 |
2022/19 | $40.39 |
2022/20 | $87.21 |
2022/24 | $95.93 |
2022/26 | $45.89 |
2022/28 | $36.23 |
2022/29 | $25.66 |
2022/30 | $152.75 |
2022/31 | $76.11 |
2022/32 | $25.12 |
2022/33 | $46.23 |
2022/39 | $84.21 |
2022/41 | $96.24 |
2022/44 | $67.67 |