quarterend - script and chart function
This function returns a value corresponding to a timestamp of the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script.
Syntax:
QuarterEnd(date[, period_no[, first_month_of_year]])
Return data type: dual
The quarterend() function determines which quarter the date falls into. It then returns a timestamp, in date format, for the last millisecond of the last month of that quarter. The first month of the year is, by default, January. However, you can change which month is set as first by using the first_month_of_year argument in the quarterend() function.
When to use it
The quarterend() function is commonly used as part of an expression when you would like the calculation to use the fraction of the quarter that has not yet occurred. For example, if you want to calculate the total interest not yet incurred during the quarter.
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. |
You can use the following values to set the first month of year in the first_month_of_year argument:
Month | Value |
---|---|
February | 2 |
March | 3 |
April | 4 |
May | 5 |
June | 6 |
July | 7 |
August | 8 |
September | 9 |
October | 10 |
November | 11 |
December |
12 |
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 | Result |
---|---|
quarterend('10/29/2005') | Returns 12/31/2005 23:59:59. |
quarterend('10/29/2005', -1) | Returns 09/30/2005 23:59:59. |
quarterend('10/29/2005', 0, 3) | Returns 11/30/2005 23:59:59. |
Example 1 - Basic example
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 in 2022 which is loaded into a table called ‘Transactions’.
-
A preceding load which contains the following:
-
The quarterend() function that is set as the ‘end_of_quarter’ field and returns a timestamp for the end of the quarter when the transactions took place.
-
The timestamp() function that is set as the ‘end_of_quarter_timestamp’ field and returns the exact timestamp of the end of the selected quarter.
-
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
quarterend(date) as end_of_quarter,
timestamp(quarterend(date)) as end_of_quarter_timestamp
;
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:
-
id
-
date
-
end_of_quarter
-
end_of_quarter_timestamp
id | date | end_of_quarter | end_of_quarter_timestamp |
---|---|---|---|
8188 | 1/7/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8189 | 1/19/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8190 | 2/5/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8191 | 2/28/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8192 | 3/16/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8193 | 4/1/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8194 | 5/7/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8195 | 5/16/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8196 | 6/15/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8197 | 6/26/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8198 | 7/9/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8199 | 7/22/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8200 | 7/23/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8201 | 7/27/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8202 | 8/2/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8203 | 8/8/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8204 | 8/19/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8205 | 9/26/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8206 | 10/14/2022 | 12/31/2022 | 12/31/2022 11:59:59 PM |
8207 | 10/29/2022 | 12/31/2022 | 12/31/2022 11:59:59 PM |
The ‘end_of_quarter’ field is created in the preceding load statement by using the quarterend() function and passing the date field as the function’s argument.
The quarterend() function initially identifies which quarter the date value falls into and then returns a timestamp for the last millisecond of that quarter.
Transaction 8203 took place on August 8. The quarterend() function identifies that the transaction took place in the third quarter, and returns the last millisecond of that quarter, which is September 30 at 11:59:59 PM.
Example 2 - period_no
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 in 2022 which is loaded into a table called ‘Transactions’.
-
A preceding load which contains the following:
-
The quarterend() function that is set as the ‘previous_quarter_end’ field and returns a timestamp for the end of the quarter before the transaction took place.
-
The timestamp() function that is set as the ‘previous_end_of_quarter_timestamp’ field and returns the exact timestamp of the end of the quarter before the transaction took place.
-
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
quarterend(date, -1) as previous_quarter_end,
timestamp(quarterend(date, -1)) as previous_quarter_end_timestamp
;
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:
-
id
-
date
-
previous_quarter_end
-
previous_quarter_end_timestamp
id | date | previous_quarter_end | previous_quarter_end_timestamp |
---|---|---|---|
8188 | 1/7/2022 | 12/31/2021 | 12/31/2021 11:59:59 PM |
8189 | 1/19/2022 | 12/31/2021 | 12/31/2021 11:59:59 PM |
8190 | 2/5/2022 | 12/31/2021 | 12/31/2021 11:59:59 PM |
8191 | 2/28/2022 | 12/31/2021 | 12/31/2021 11:59:59 PM |
8192 | 3/16/2022 | 12/31/2021 | 12/31/2021 11:59:59 PM |
8193 | 4/1/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8194 | 5/7/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8195 | 5/16/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8196 | 6/15/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8197 | 6/26/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8198 | 7/9/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8199 | 7/22/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8200 | 7/23/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8201 | 7/27/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8202 | 8/2/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8203 | 8/8/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8204 | 8/19/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8205 | 9/26/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8206 | 10/14/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8207 | 10/29/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
Because a period_no of -1 is used as the offset argument in the quarterend() function, the function first identifies the quarter that the transactions take place in. It then shifts one quarter prior and identifies the final millisecond of that quarter.
Transaction 8203 took place on August 8. The quarterend() function identifies that the quarter before the transaction took place was between April 1 and June 30. The function then returns the final millisecond of that quarter, June 30 at 11:59:59 PM.
Example 3 - first_month_of_year
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 in 2022 which is loaded into a table called ‘Transactions’.
-
A preceding load which contains the following:
-
The quarterend() function that is set as the ‘end_of_quarter’ field and returns a timestamp for the end of the quarter when the transactions took place.
-
The timestamp() function that is set as the ‘end_of_quarter_timestamp’ field and returns the exact timestamp of the end of the selected quarter.
-
However, in this example, the company policy is that the financial year begins on March 1.
Load script
SET DateFormat='MM/DD/YYYY';
Transactions:
Load
*,
quarterend(date, 0, 3) as end_of_quarter,
timestamp(quarterend(date, 0, 3)) as end_of_quarter_timestamp
;
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
id | date | end_of_quarter | end_of_quarter_timestamp |
---|---|---|---|
8188 | 1/7/2022 | 02/28/2022 | 2/28/2022 11:59:59 PM |
8189 | 1/19/2022 | 02/28/2022 | 2/28/2022 11:59:59 PM |
8190 | 2/5/2022 | 02/28/2022 | 2/28/2022 11:59:59 PM |
8191 | 2/28/2022 | 02/28/2022 | 2/28/2022 11:59:59 PM |
8192 | 3/16/2022 | 05/31/2022 | 5/31/2022 11:59:59 PM |
8193 | 4/1/2022 | 05/31/2022 | 5/31/2022 11:59:59 PM |
8194 | 5/7/2022 | 05/31/2022 | 5/31/2022 11:59:59 PM |
8195 | 5/16/2022 | 05/31/2022 | 5/31/2022 11:59:59 PM |
8196 | 6/15/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8197 | 6/26/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8198 | 7/9/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8199 | 7/22/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8200 | 7/23/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8201 | 7/27/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8202 | 8/2/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8203 | 8/8/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8204 | 8/19/2022 | 08/31/2022 | 8/31/2022 11:59:59 PM |
8205 | 9/26/2022 | 11/30/2022 | 11/30/2022 11:59:59 PM |
8206 | 10/14/2022 | 11/30/2022 | 11/30/2022 11:59:59 PM |
8207 | 10/29/2022 | 11/30/2022 | 11/30/2022 11:59:59 PM |
Because the first_month_of_year argument of 3 is used in the quarterend() function, the start of the year moves from January 1 to March 1.
Transaction 8203 took place on August 8. Because the beginning of the year is March 1, the quarters in the year occur between Mar-May, Jun-Aug, Sep-Nov, and Dec-Feb.
The quarterend() function identifies that the transaction took place in the quarter between the start of June and of August and returns the last millisecond of that quarter, which is August 31 at 11:59:59 PM.
Example 4 - Chart object example
Overview
The same dataset and scenario as the first example are used.
However, in this example, the dataset is unchanged and 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 in the app.
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
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
id
-
date
To calculate the end date of the quarter that a transaction takes place in, create the following measures:
-
=quarterend(date)
-
=timestamp(quarterend(date))
id | date | =quarterend(date) | =timestamp(quarterend(date)) |
---|---|---|---|
8188 | 1/7/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8189 | 1/19/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8190 | 2/5/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8191 | 2/28/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8192 | 3/16/2022 | 03/31/2022 | 3/31/2022 11:59:59 PM |
8193 | 4/1/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8194 | 5/7/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8195 | 5/16/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8196 | 6/15/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8197 | 6/26/2022 | 06/30/2022 | 6/30/2022 11:59:59 PM |
8198 | 7/9/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8199 | 7/22/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8200 | 7/23/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8201 | 7/27/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8202 | 8/2/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8203 | 8/8/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8204 | 8/19/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8205 | 9/26/2022 | 09/30/2022 | 9/30/2022 11:59:59 PM |
8206 | 10/14/2022 | 12/31/2022 | 12/31/2022 11:59:59 PM |
8207 | 10/29/2022 | 12/31/2022 | 12/31/2022 11:59:59 PM |
The ‘end_of_quarter’ field is created in the preceding load statement by using the quarterend() function and passing the date field as the function’s argument.
The quarterend() function initially identifies which quarter the date value falls into and then returns a timestamp for the last millisecond of that quarter.
Transaction 8203 took place on August 8. The quarterend() function identifies that the transaction took place in the third quarter, and returns the last millisecond of that quarter, which is September 30 at 11:59:59 PM.
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 is loaded into a table called ‘Employee_Expenses’. The table contains the following fields:
-
Employee IDs
-
Employee names
-
The average daily expense claims of each employee.
-
The end user would like a chart object that displays, by employee id and employee name, the estimated expense claims still to be incurred for the remainder of the quarter. The financial year begins in January.
Load script
Employee_Expenses:
Load
*
Inline
[
employee_id,employee_name,avg_daily_claim
182,Mark, $15
183,Deryck, $12.5
184,Dexter, $12.5
185,Sydney,$27
186,Agatha,$18
];
Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
-
employee_id
-
employee_name
To calculate the accumulated interest, create the following measure:
-
=(quarterend(today(1))-today(1))*avg_daily_claim
Set the measure’s Number Formatting to Money.
employee_id | employee_name | =(quarterend(today(1))-today(1))*avg_daily_claim |
---|---|---|
182 | Mark | $480.00 |
183 | Deryck | $400.00 |
184 | Dexter | $400.00 |
185 | Sydney | $864.00 |
186 | Agatha | $576.00 |
The quarterend() function uses today’s date as its only argument and returns the end date of the current month. Then, it subtracts today’s date from the year end date, and the expression returns the number of days that remain this month.
This value is then multiplied by the average daily expense claim of each employee to calculate the estimated value of claims each employee is expected to make in the remaining quarter.