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 field in a data table:

  • StringDate

• The DateFormat variable, which uses the M/D/YY format.

• A preceding load that creates a new field called Date. The Date# and Num functions are used to convert the string to a date, and then return the value as a number.

Load script

SET DateFormat='M/D/YY';
Example:
Load *,
Num(Date#(StringDate, 'D/M/YYYY')) as Date
;
LOAD * INLINE [
StringDate
13/12/2024
31/1/23
];

Results

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

• StringDate

• Date

Create the following measures:

• =Date(Date), to view the value as a date that is formatted using the date format variable in the load script.

• =IsNum(StringDate), to verify that the value is a string and not evaluated as a date because the date format of the field values do not match the system setting.

The results show that the Date field in column 2, created by the preceding load, has converted the StringDate values to a date that is expressed as a number. The Date format function in column 3 recognizes the Date values as dates and formats them according to the DateFormat variable in the load script.

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:

  • Customer
  • BirthDate

• A preceding load that creates a new field called ParsedBirthDate.

Load script

Example:
Load *,
Date#(BirthDate, 'MM.DD.YYYY') as ParsedBirthDate;
Load * inline [
Customer, BirthDate
Customer A, 08.25.2022
Customer B, 08.25.2022
Customer C, 08.25.2022
];

Results

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

• Customer

• BirthDate

• ParsedBirthDate

Create the following measure:

• =Date(ParsedBirthDate,'DD/MM/YYYY'), to view the value as a formatted date.

In this example we can see that in column 2, the BirthDate field is treated as text by the system. The Date# function used in the preceding load converts BirthDate to a recognizable date in column 3, ParsedBirthDate. The Date format function in column 4 can then display the ParsedBirthDate values in the format DD/MM/YYYY.