This function returns
a display value showing the months of the quarter (formatted according
to the MonthNames script variable) and year with an underlying numeric
value corresponding to a timestamp of the first millisecond of the first
day of the quarter.
The quartername() function determines which quarter the date falls into. It then returns a value showing the start-end months of this quarter as well as the year. The underlying numeric value of this result is the first millisecond of the quarter.
Arguments
Argument
Description
date
The date or timestamp to evaluate.
period_no
period_no is an integer, where the value
0 indicates the quarter which contains date.
Negative values in period_no indicate
preceding quarters and positive values indicate succeeding quarters.
first_month_of_year
If you
want to work with (fiscal) years not starting in January, indicate
a value between 2 and 12 in first_month_of_year.
When to use it
The quartername() function is useful when you would like to compare aggregations by quarter. For example, if you would like to see the total sales of products by quarter.
This function could be used in the load script to create a field in a Master Calendar table. Alternatively, it could be used directly in a chart as a calculated dimension.
These examples use the date format MM/DD/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.
Function examples
Example
Result
quartername('10/29/2013')
Returns Oct-Dec 2013.
quartername('10/29/2013', -1)
Returns Jul-Sep 2013.
quartername('10/29/2013', 0, 3)
Returns Sep-Nov 2013.
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, transaction_quarter, which returns the quarter in which the transactions took place.
Add your other text here, as needed, with lists etc.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
transaction_quarter
Results table
date
transaction_quarter
1/7/2022
Jan-Mar 2022
1/19/2022
Jan-Mar 2022
2/5/2022
Jan-Mar 2022
2/28/2022
Jan-Mar 2022
3/16/2022
Jan-Mar 2022
4/1/2022
Apr-Jun 2022
5/7/2022
Apr-Jun 2022
5/16/2022
Apr-Jun 2022
6/15/2022
Apr-Jun 2022
6/26/2022
Apr-Jun 2022
7/9/2022
Jul-Sep 2022
7/22/2022
Jul-Sep 2022
7/23/2022
Jul-Sep 2022
7/27/2022
Jul-Sep 2022
8/2/2022
Jul-Sep 2022
8/8/2022
Jul-Sep 2022
8/19/2022
Jul-Sep 2022
9/26/2022
Jul-Sep 2022
10/14/2022
Oct-Dec 2022
10/29/2022
Oct-Dec 2022
The transaction_quarter field is created in the preceding load statement by using the quartername() function and passing the date field as the function’s argument.
The quartername() function initially identifies the quarter into which the date value falls. It then returns a value showing the start-end months of this quarter, as well as the year.
Transaction 8203 took place on August 8, 2022. The quartername() function identifies that the transaction took place in the third quarter, and therefore returns Jul-Sep 2022. The months are displayed in the same format as the MonthNames system variable.
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_quarter, that that returns the previous quarter to when the transactions took place.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
previous_quarter
Results table
date
previous_quarter
1/7/2022
Oct-Dec 2021
1/19/2022
Oct-Dec 2021
2/5/2022
Oct-Dec 2021
2/28/2022
Oct-Dec 2021
3/16/2022
Oct-Dec 2021
4/1/2022
Jan-Mar 2022
5/7/2022
Jan-Mar 2022
5/16/2022
Jan-Mar 2022
6/15/2022
Jan-Mar 2022
6/26/2022
Jan-Mar 2022
7/9/2022
Apr-Jun 2022
7/22/2022
Apr-Jun 2022
7/23/2022
Apr-Jun 2022
7/27/2022
Apr-Jun 2022
8/2/2022
Apr-Jun 2022
8/8/2022
Apr-Jun 2022
8/19/2022
Apr-Jun 2022
9/26/2022
Apr-Jun 2022
10/14/2022
Jul-Sep 2022
10/29/2022
Jul-Sep 2022
In this instance, because a period_no of -1 was used as the offset argument in the quartername() function, the function first identifies that the transactions took place in the third quarter. It then shifts one quarter prior and returns a value showing the start-end months of this quarter, as well as the year.
Transaction 8203 took place on August 8. The quartername() function identifies that the quarter before the transaction took place was between April 1 and June 30. Therefore, it returns Apr-Jun 2022.
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. However, in this example, we need to set March 1 as the beginning of the financial year.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
transaction_quarter
Results table
date
transaction_quarter
1/7/2022
Dec-Feb 2021
1/19/2022
Dec-Feb 2021
2/5/2022
Dec-Feb 2021
2/28/2022
Dec-Feb 2021
3/16/2022
Mar-May 2022
4/1/2022
Mar-May 2022
5/7/2022
Mar-May 2022
5/16/2022
Mar-May 2022
6/15/2022
Jun-Aug 2022
6/26/2022
Jun-Aug 2022
7/9/2022
Jun-Aug 2022
7/22/2022
Jun-Aug 2022
7/23/2022
Jun-Aug 2022
7/27/2022
Jun-Aug 2022
8/2/2022
Jun-Aug 2022
8/8/2022
Jun-Aug 2022
8/19/2022
Jun-Aug 2022
9/26/2022
Sep-Nov 2022
10/14/2022
Sep-Nov 2022
10/29/2022
Sep-Nov 2022
In this instance, because the first_month_of_year argument of 3 is used in the quartername() function, the start of the year moves from January 1 to March 1. Therefore, the quarters in the year are separated into March-May, June-August, September-November and December-February.
Transaction 8203 took place on August 8. The quartername() function identifies that the transaction took place in the second quarter, between the start of June and the end of August. Therefore, it returns Jun-Aug 2022.
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 a timestamp for the end of the quarter when the transactions took place is created as a measure in a chart object of the application.
Load the data and open a sheet. Create a new table and add this field as a dimension: date.
Create the following measure:
=quartername(date)
Results table
date
=quartername(date)
1/7/2022
Jan-Mar 2022
1/19/2022
Jan-Mar 2022
2/5/2022
Jan-Mar 2022
2/28/2022
Jan-Mar 2022
3/16/2022
Jan-Mar 2022
4/1/2022
Apr-Jun 2022
5/7/2022
Apr-Jun 2022
5/16/2022
Apr-Jun 2022
6/15/2022
Apr-Jun 2022
6/26/2022
Apr-Jun 2022
7/9/2022
Jul-Sep 2022
7/22/2022
Jul-Sep 2022
7/23/2022
Jul-Sep 2022
7/27/2022
Jul-Sep 2022
8/2/2022
Jul-Sep 2022
8/8/2022
Jul-Sep 2022
8/19/2022
Jul-Sep 2022
9/26/2022
Jul-Sep 2022
10/14/2022
Oct-Dec 2022
10/29/2022
Oct-Dec 2022
The transaction_quarter measure is created in the chart object by using the quartername() function and passing the date field as the function’s argument.
The quartername() function initially identifies the quarter into which the date value falls. It then returns a value showing the start-end months of this quarter, as well as the year.
Transaction 8203 took place on August 8, 2022. The quartername() function identifies that the transaction took place in the third quarter, and therefore returns Jul-Sep 2022. The months are displayed in the same format as the MonthNames system variable.
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 quarter for the transactions. This could be achieved even when this dimension is not available in the data model, using the quartername() function as a calculated dimension in the chart.