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. For more information, see Modifying regional settings for apps and scripts.
Default regional settings in apps are based on the user profile. These regional format settings are not related to the language displayed in the Qlik Cloud user interface. Qlik Cloud will be displayed in the same language as the browser you are using.
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.
Diagram of quartername() function, example with no additional arguments
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.
Diagram of quartername() function, period_no example
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.
Diagram of quartername() function, first_week_day example
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.
Diagram of quartername() function, chart object example
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.
Load the data and open a sheet. Create a new table.
Create a calculated dimension using the following expression:
=quartername(date)
Next, calculate total sales using the following aggregation measure:
=sum(amount)
Set the measure's Number formatting to Money.
Results table
=quartername(date)
=sum(amount)
Jul-Sep 2022
$446.31
Apr-Jun 2022
$351.48
Jan-Mar 2022
$253.89
Oct-Dec 2022
$163.91
Load script
The load script is a sequence of statements that defines what data to load and how to link the different loaded tables. It can be generated with the Data manager, or with the Data load editor, where it also can be viewed and edited.
A field contains values, loaded from a data source. At a basic level, a field corresponds to a column in a table. Fields are used to create dimensions and measures in visualizations.
Charts are objects where calculations, aggregations, and groupings can be made. Graphical visualizations, such as bar charts and pie charts are common examples, but also non-graphical objects such as pivot tables are charts.
A chart consists of dimensions and measures, where the measures are calculated once per dimensional value. If the chart contains multiple dimensions, the measures are calculated once per combination of dimensional values.
A dimension is an entity used to categorize data in a chart. For example, the slices in a pie chart or the bars of a bar chart represent individual values in a dimension. Dimensions are often a single field with discrete values, but can also be calculated in an expression.
A dimension is a dataset in a data mart that forms part of the star schema. Dimension datasets hold the descriptive information for all related fields that are included in the fact table’s records. A few common examples of dimension datasets are Customer and Product. Since the data in a dimension dataset is often denormalized, dimension datasets have a large number of columns.
The term dataset is sometimes synonymous with table. It can refer to the original source table, the table after undergoing transformations, or the fact and dimension tables in a data mart.
It can also refer to a logical table, where there are several instance tables and views:
Current data
History, which holds previous versions of the table
A preceding load is a script construct that allows you to load from the following LOAD or SELECT statement without specifying that source. Preceding loads are often faster than resident loads.
A measure is a calculation base on one ore more aggregations. For example, the sum of sales is a single aggregation, while the sum of sales divided by the count of customers is a measure based on two aggregations.
Sheets are components of Qlik Sense apps. They present visualizations to app users so they can explore, analyze, and discover data. Sheets can be public or private.
An aggregation is a calculation using multiple records in the source tables. Often it is a single field aggregated with a function such as sum, count, min, max, or average. For example, the sum of sales.
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.