This function returns a date calculated from the year YYYY, the month MM and the day DD.
Syntax:
MakeDate(YYYY [ , MM [ , DD ] ])
Return data type: dual
Arguments
Argument
Description
YYYY
The year as an integer.
MM
The month as an integer. If no month is stated, 1 (January) is assumed.
DD
The day as an integer. If no day is stated, 1 (the 1st) is assumed.
When to use it
The makedate() function would commonly be used in the script for data generation to generate a calendar. This could also be used when the date field is not directly available as date, but needs some transformations to extract year, month and day components.
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
makedate(2012)
Returns 01/01/2012.
makedate(12)
Returns 01/01/2012.
makedate(2012,12)
Returns 12/01/2012.
makedate(2012,2,14)
Returns 02/14/2012.
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 – 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 for 2018, 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_date, that returns a date in the format MM/DD/YYYY.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
transaction_year
transaction_month
transaction_day
transaction_date
Results table
transaction_year
transaction_month
transaction_day
transaction_date
2018
08
30
08/30/2018
2018
09
07
09/07/2018
2018
09
16
09/16/2018
2018
09
22
09/22/2018
2018
09
23
09/23/2018
The transaction_date field is created in the preceding load statement by using the makedate() function and passing the year, month, day fields as function arguments.
The function then combines and converts these values into a date field, returning the results in the format of the DateFormat system variable.
Example 2 – Modified DateFormat
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, transaction_date, in the format DD/MM/YYYY without modifying the DateFormat system variable.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
transaction_year
transaction_month
transaction_day
transaction_date
Results table
transaction_year
transaction_month
transaction_day
transaction_date
2018
08
30
30/08/2018
2018
09
07
07/09/2018
2018
09
16
16/09/2018
2018
09
22
22/09/2018
2018
09
23
23/09/2018
In this instance, the makedate() function is nested inside the date() function. The second argument of the date() function sets the format of the makedate() function results as the required DD/MM/YYYY.
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:
A dataset containing a set of transactions for 2018, which is loaded into a table called Transactions.
The transaction dates provided across two fields: year and month.
Create a chart object measure transaction_date, that returns a date in the format MM/DD/YYYY.
Load the data and open a sheet. Create a new table and add these fields as dimensions:
year
month
To determine the transaction_date, create this measure:
=makedate(transaction_year,transaction_month)
Results table
transaction_year
transaction_month
transaction_date
2018
08
08/01/2018
2018
09
09/01/2018
The transaction_date measure is created in the chart object by using the makedate() function, and passing the year and month fields as function arguments.
The function then combines these values, as well as the assumed day value of 01. These values are then converted into a date field, returning the results in the format of the DateFormat system variable.
Example 4 – Scenario
Overview
Create a calendar dataset for the calendar year of 2022.
Load script
SET DateFormat='MM/DD/YYYY';
Calendar:
load
*
where year(date)=2022;
load
date(recno()+makedate(2021,12,31)) as date
AutoGenerate 400;
Results
Results table
date
01/01/2022
01/02/2022
01/03/2022
01/04/2022
01/05/2022
01/06/2022
01/07/2022
01/08/2022
01/09/2022
01/10/2022
01/11/2022
01/12/2022
01/13/2022
01/14/2022
01/15/2022
01/16/2022
01/17/2022
01/18/2022
01/19/2022
01/20/2022
01/21/2022
01/22/2022
01/23/2022
01/24/2022
01/25/2022
+ 340 more rows
The makedate() function creates a date value for December 31, 2021. The recno() function provides the record number of the current record being loaded into the table, starting from 1. Therefore, the first record has the date January 1, 2022. Each successive recno() will then increment this date by 1. This expression is wrapped in a date() function to convert the value into a date. This process is repeated 400 times by the autogenerate function. Finally, by using a preceding load, a where condition can be used to only load dates from year 2022. This script generates a calendar containing every date in 2022.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!