daystart - 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 simple dataset containing a list of dates, which is loaded into a table named Calendar.

• The default TimeStampFormat system variable (M/D/YYYY h:mm:ss[.fff] TT) is used.

• A preceding load which creates an additional field named SOD_timestamp, using the daystart() function.

Aside from the date, no additional parameters are provided to the function.

Load script

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Calendar:
    Load
        date,
        daystart(date) as SOD_timestamp
        ;
Load
date
Inline 
[
date
03/11/2022 1:47:15 AM
03/12/2022 4:34:58 AM
03/13/2022 5:15:55 AM
03/14/2022 9:25:14 AM
03/15/2022 10:06:54 AM
03/16/2022 10:44:42 AM
03/17/2022 11:33:30 AM
03/18/2022 12:58:14 PM
03/19/2022 4:23:12 PM
03/20/2022 6:42:15 PM
03/21/2022 7:41:16 PM
];
					

Results

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

• date

• SOD_timestamp

As can be seen in the table above, the end of day timestamp is generated for each date in our dataset. The timestamp is in the format of the system variable TimestampFormat M/D/YYYY h:mm:ss[.fff] TT.

Overview

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

The load script contains:

• A dataset containing parking fines, which is loaded into a table named Fines. The dataset includes the following fields:

  • id

  • due_date

  • number_plate

  • amount

• A preceding load using the daystart() function and supplying all three parameters: time, period_no, and day_start. This preceding load creates the following two new date fields:

  • An early_repayment_period date field, beginning seven days before the payment is due.

  • A late_penalty_period date field, beginning 14 days after the payment is due.

Load script

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Fines:
    Load
        *,
        daystart(due_date,-7) as early_repayment_period,
        daystart(due_date,14) as late_penalty_period
        ;
Load
*
Inline
[
id, due_date, number_plate,amount
1,02/11/2022, 573RJG,50.00
2,03/25/2022, SC41854,50.00
3,04/14/2022, 8EHZ378,50.00
4,06/28/2022, 8HSS198,50.00
5,08/15/2022, 1221665,50.00
6,11/16/2022, EAK473,50.00
7,01/17/2023, KD6822,50.00
8,03/22/2023, 1GGLB,50.00
];

Results

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

• due_date

• early_repayment_period

• late_penalty_period

The values of the new fields are in the TimestampFormat M/DD/YYYY tt. Because the function daystart() was used, the timestamp values are all the first millisecond of the day.

The early repayment period values are seven days before the due date, as a result of the second argument being passed in the daystart() function being negative.

The late repayment period values are 14 days after the due date, as a result of the second argument being passed in the daystart() function being positive.

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 previous example.

• The same preceding load as the previous example.

In this example, we set the working day to begin and end at 7:00 AM each day.

Load script

SET DateFormat='MM/DD/YYYY';

Fines:
    Load
        *,
        daystart(due_date,-7,7/24) as early_repayment_period,	 
        daystart(due_date,14, 7/24) as late_penalty_period
        ;
Load
*
Inline
[
id, due_date, number_plate,amount
1,02/11/2022, 573RJG,50.00
2,03/25/2022, SC41854,50.00
3,04/14/2022, 8EHZ378,50.00
4,06/28/2022, 8HSS198,50.00
5,08/15/2022, 1221665,50.00
6,11/16/2022, EAK473,50.00
7,01/17/2023, KD6822,50.00
8,03/22/2023, 1GGLB,50.00
];

Results

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

• due_date

• early_repayment_period

• late_penalty_period

The dates now have a timestamp of 7:00 AM because the value of the day_start argument which was passed into the daystart() function was 7/24. This sets the beginning of the day to 7:00 AM.

Because the due_date field does not have a timestamp, it is treated as 12:00 AM, which is thus still part of the previous day, since the days start and end at 7:00 AM. Therefore, the early repayment period for a fine due on February 11 begins on February 3 at 7:00 AM.

Overview

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

This example uses the same dataset and scenario as the previous example.

However, only the original Fines table is loaded into the application, with the two additional due dates values being calculated in a chart object.

Load script

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

Fines:
    Load
*
Inline 
[
id, due_date, numer_plate,amount
1,02/11/2022 9:25:14 AM, 573RJG,50.00
2,03/25/2022 10:06:54 AM, SC41854,50.00
3,04/14/2022 10:44:42 AM, 8EHZ378,50.00
4,06/28/2022 11:33:30 AM, 8HSS198,50.00
5,08/15/2022 12:58:14 PM, 1221665,50.00
6,11/16/2022 4:23:12 PM, EAK473,50.00
7,01/17/2023 6:42:15 PM, KD6822,50.00
8,03/22/2023 7:41:16 PM, 1GGLB,50.00
];

Results

Do the following:

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

2. To create the early_repayment_period field, create the following measure.

   =daystart(due_date,-7,7/24)

3. To create the late_penalty_period field, create the following measure:

   =daystart(due_date,14,7/24)

The values of the new fields are in the TimestampFormat M/D/YYYY h:mm:ss[.fff] TT. Because the daystart() function was used, the timestamp values correspond to the first millisecond of the day.

The early repayment period values are seven days before the due date, since the second argument passed in the daystart() function was negative.

The late repayment period values are 14 days after the due date, since the second argument passed in the daystart() function was positive.

The dates have a timestamp of 7:00 AM because the value of the third argument passed into the daystart() function, day_start, was 7/24.