This function returns the date occurring n months after startdate or, if
n is negative, the date occurring
n months before startdate.
Syntax:
AddMonths(startdate, n
, [ , mode])
Return data type: dual
The addmonths() function adds or subtracts a defined number of months, n, from a startdate and returns the resultant date.
The mode argument will impact startdate values on or after the 28th of the month. By setting the mode argument to 1, the addmonths() function returns a date that is equal in relative distance to the end of the month as the startdate.
For example, February 28 is the last day of the month. If the addmonths() function, with a mode of 1, is used to return the date two months later, the function will return the last date of April, April 30.
Arguments
Argument
Description
startdate
The start date as a time stamp, for example '2012-10-12'.
n
Number of months as a positive or negative integer.
mode
Specifies if the month is added relative to the beginning or to the end of the month. Default mode is 0 for additions relative to the beginning of the month. Set mode to 1 for additions relative to the end of the month. When mode is set to 1 and the input date is the 28th or above, the function checks how many days are left to reach the end of the month on the startdate. The same number of days to reach the end of the month are set on the date returned.
When to use it
The addmonths() function will commonly be used in an expression to find a date a given number of months before or after a period of time.
For example, the addmonths() function can be used to identify the end date of mobile phone contracts.
Function examples
Example
Result
addmonths ('01/29/2003' ,3)
Returns '04/29/2003'.
addmonths ('01/29/2003',3,0)
Returns '04/29/2003'.
addmonths ('01/29/2003',3,1)
Returns '04/28/2003'.
addmonths ('01/29/2003',1,0)
Returns '02/28/2003'.
addmonths ('01/29/2003',1,1)
Returns '02/26/2003'.
addmonths ('02/28/2003',1,0)
Returns '03/28/2003'.
addmonths ('02/28/2003',1,1)
Returns '03/31/2003'.
addmonths ('01/29/2003',-3)
Returns '10/29/2002'.
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 – 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 between 2020 and 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, two_months_later, that returns the date for two months after the transaction took place.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
two_months_later
Results table
date
two_months_later
01/10/2020
03/10/2020
02/28/2020
04/28/2020
04/09/2020
06/09/2020
04/16/2020
06/16/2020
05/21/2020
07/21/2020
08/14/2020
10/14/2020
10/07/2020
12/07/2020
12/05/2020
02/05/2021
01/22/2021
03/22/2021
02/03/2021
04/03/2021
03/17/2021
05/17/2021
04/23/2021
06/23/2021
05/04/2021
07/04/2021
06/30/2021
08/30/2021
07/26/2021
09/26/2021
12/27/2021
02/27/2022
02/02/2022
04/02/2022
02/26/2022
04/26/2022
03/07/2022
05/07/2022
03/11/2022
05/11/2022
The two_months_later field is created in the preceding load statement by using the addmonths() function. The first argument provided identifies which date is being evaluated. The second argument is the number of months to add or subtract from the startdate. In this instance, the value of 2 is provided.
Transaction 8193 took place on August 14. Therefore, the addmonths() function returns October 14, 2020 for the two_months_later field.
Example 2 – Relative month end
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 month-end transactions in 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, relative_two_months_prior, that returns the relative month-end date for two months before the transaction took place.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
date
relative_two_months_prior
Results table
date
relative_two_months_prior
01/28/2022
11/27/2021
01/31/2022
11/30/2021
02/28/2022
12/31/2021
04/29/2022
02/27/2022
04/30/2022
02/28/2022
05/31/2022
03/31/2022
08/14/2022
06/14/2022
10/07/2022
08/07/2022
The relative_two_months_prior field is created in the preceding load statement by using the addmonths() function. The first argument provided identifies which date is being evaluated. The second argument is the number of months to add or subtract from the startdate. In this instance, the value of -2 is provided. The final argument is the mode, with a value of 1, which forces the function to calculate the relative month-end date for all dates greater than or equal to 28.
Transaction 8191 takes place on April 29, 2022. Initially, two months prior would set the month to February. Then, due to the third argument of the function setting the mode to 1 and the day value being later than the 27th, the function calculates the relative month-end value. The function identifies that the 29th is the second last day of April and therefore returns the second last day of February, the 27th.
Example 3– 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 date for two months after the transaction took place is created as a measure in a chart object.
Load the data and open a sheet. Create a new table and add this field as a dimension: date.
Create the following measure:
=addmonths(date,2)
Results table
date
=addmonths(date,2)
01/10/2020
03/10/2020
02/28/2020
04/28/2020
04/09/2020
06/09/2020
04/16/2020
06/16/2020
05/21/2020
07/21/2020
08/14/2020
10/14/2020
10/07/2020
12/07/2020
12/05/2020
02/05/2021
01/22/2021
03/22/2021
02/03/2021
04/03/2021
03/17/2021
05/17/2021
04/23/2021
06/23/2021
05/04/2021
07/04/2021
06/30/2021
08/30/2021
07/26/2021
09/26/2021
12/27/2021
02/27/2022
02/02/2022
04/02/2022
02/26/2022
04/26/2022
03/07/2022
05/07/2022
03/11/2022
05/11/2022
The two_months_later measure is created in the chart object by using the addmonths() function. The first argument provided identifies which date is being evaluated. The second argument is the number of months to add or subtract from the startdate. In this instance, the value of 2 is provided.
Transaction 8193 took place on August 14. Therefore, the addmonths() function returns the October 14, 2020 for the two_months_later field.
Example 4 – Scenario
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a table called Mobile_Plans.
Information with the contract ID, start date, contract length, and monthly fee.
The end user would like a chart object that displays, by contract ID, the termination date of each phone contract.