Date - script and chart function

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • RowNo
  • InputDate

• The DateFormat variable, which uses the YY-MM-DD format.

Load script

SET DateFormat='YY-MM-DD';
Example:
Load * inline [
RowNo, InputDate
1, 35648
2, 35648.375
3, 8/6/97
];Copy code to clipboard

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• RowNo

• InputDate

Create the following measures:

• =Date(InputDate), to view the value as a date that is formatted using the DateFormat variable settings.

• =Date(InputDate, 'DD.MM.YYYY'), to view the value as a date that is formatted using the format supplied in the function.

• =Date(InputDate, 'YY.MM.DD'), to view the value as a date that is formatted using the format supplied in the function.

• =Num(InputDate), to return the numeric value of the date.

The results show how the Date function can be used to display and format dates. In this example, the DateFormat is set as YY-MM-DD in the load script. The system is expecting the expression for the date to use this format. You can see that the value for InputDate in row 3 (8/6/97) does not follow this format, therefore the system does not recognize it as a date and returns NULL.

Change the DateFormat setting in the load script to SET DateFormat='M/D/YY'; and reload the data. The value in row 3 will now match the format that is expected. The system will recognize it as a valid date and format it accordingly. The following table shows the results with the new DateFormat setting.

Overview

A dataset contains sales data that uses the date format MM-DD-YYYY. You want to use the date format DD/MM/YYYY.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Customer
  • SalesDate
  • Amount

• The DateFormat variable, which uses the MM/DD/YYYY format.

Load script

SET DateFormat='MM/DD/YYYY';
Example:
Load * inline [
Customer, SalesDate, Amount
Customer A, 2/15/2024, 100008
Customer B, 9/30/2024, 12500
Customer C, 12/28/2024, 15000
];Copy code to clipboard

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

• Customer

Create the following measures:

• =Date(SalesDate ,'DD/MM/YYYY') to view the value as a date that is formatted using the format supplied in the function.

• =Sum(Amount) to calculate the total value of the cost of sales.

Looking at the results, you can see that although the dates in the dataset used one format, the Date function allows you to present these dates in a different format.