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.
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.
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.