makedate - script and chart function

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 script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        makedate(transaction_year, transaction_month, transaction_day) as transaction_date
        ; 
Load * Inline [
transaction_id, transaction_year, transaction_month, transaction_day, transaction_amount, transaction_quantity, customer_id
3750, 2018, 08, 30, 12423.56, 23, 2038593
3751, 2018, 09, 07, 5356.31, 6, 203521
3752, 2018, 09, 16, 15.75, 1, 5646471
3753, 2018, 09, 22, 1251, 7, 3036491
3754, 2018, 09, 22, 21484.21, 1356, 049681
3756, 2018, 09, 22, -59.18, 2, 2038593
3757, 2018, 09, 23, 3177.4, 21, 203521
];

Results

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

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.

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 script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        date(makedate(transaction_year, transaction_month, transaction_day), ‘DD/MM/YYYY’) as transaction_date
        ; 
Load * Inline [
transaction_id, transaction_year, transaction_month, transaction_day, transaction_amount, transaction_quantity, customer_id
3750, 2018, 08, 30, 12423.56, 23, 2038593
3751, 2018, 09, 07, 5356.31, 6, 203521
3752, 2018, 09, 16, 15.75, 1, 5646471
3753, 2018, 09, 22, 1251, 7, 3036491
3754, 2018, 09, 22, 21484.21, 1356, 049681
3756, 2018, 09, 22, -59.18, 2, 2038593
3757, 2018, 09, 23, 3177.4, 21, 203521
];

Results

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

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.

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 script

SET DateFormat='MM/DD/YYYY';

Transactions:
Load * Inline [
transaction_id, transaction_year, transaction_month, transaction_amount, transaction_quantity, customer_id
3750, 2018, 08, 12423.56, 23, 2038593
3751, 2018, 09, 5356.31, 6, 203521
3752, 2018, 09, 15.75, 1, 5646471
3753, 2018, 09, 1251, 7, 3036491
3754, 2018, 09, 21484.21, 1356, 049681
3756, 2018, 09, -59.18, 2, 2038593
3757, 2018, 09, 3177.4, 21, 203521
];

Results

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)

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.

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

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.