year - 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 of dates, which is loaded into a table named Master Calendar.

• The default DateFormat system variable (MM/DD/YYYY) is used.

• A preceding load which is used to create an additional field, year, using the year() function.

Load script

SET DateFormat='MM/DD/YYYY';

Master_Calendar:

    Load
        date,
        year(date) as year
        ;
Load
date
Inline 
[
date
12/28/2020
12/29/2020
12/30/2020
12/31/2020
01/01/2021
01/02/2021
01/03/2021
01/04/2021
01/05/2021
01/06/2021
01/07/2021
];

Results

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

• date

• year

Overview

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

The load script contains:

• A dataset of dates, which is loaded into a table named Master Calendar.

• The default DateFormat system variable (MM/DD/YYYY) is used. However, the dates included in the dataset are in ANSI standard date format.

• A preceding load, which is used to create an additional field, named year, using the year() function.

Load script

SET DateFormat='MM/DD/YYYY';

Master_Calendar:
    Load
        date,
        year(date) as year
        ;
Load
date
Inline 
[
date
2020-12-28
2020-12-29
2020-12-30
2020-12-31
2021-01-01
2021-01-02
2021-01-03
2021-01-04
2021-01-05
2021-01-06
2021-01-07
];

Results

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

• date

• year

Overview

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

The load script contains:

• A dataset of dates in numerical format, which is loaded into a table named Master Calendar.

• The default DateFormat system variable (MM/DD/YYYY) is used.

• A preceding load, which is used to create an additional field, year, using the year() function.

The original unformatted date is loaded, named unformatted_date, and to provide clarity, a further additional field, named long_date, is used to convert the numerical date into a formatted date field using the date() function.

Load script

SET DateFormat='MM/DD/YYYY';

Master_Calendar:
    Load
        unformatted_date,
        date(unformatted_date) as long_date,
        year(unformatted_date) as year
        ;
Load
unformatted_date
Inline 
[
unformatted_date
44868
44898
44928
44958
44988
45018
45048
45078
45008
45038
45068
];

Results

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

• unformatted_date

• long_date

• year

Overview

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

In this example, a dataset of orders placed is loaded into a table named Sales. The table contains three fields:

• id

• sales_date

• amount

Warranties on product sales last two years from the date of sale. The task is to create a measure in a chart to determine the year in which each warranty will expire.

Load script

Sales:
Load
id,
sales_date,
amount
Inline 
[
id,sales_date,amount
1,12/28/2020,231.24,
2,12/29/2020,567.28,
3,12/30/2020,364.28,
4,12/31/2020,575.76,
5,01/01/2021,638.68,
6,01/02/2021,785.38,
7,01/03/2021,967.46,
8,01/04/2021,287.67
9,01/05/2021,764.45,
10,01/06/2021,875.43,
11,01/07/2021,957.35
];

Results

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

Create the following measure:

=year(sales_date+365*2)

The results of this measure can be seen in the table above. To add two years to a date, multiply 365 by 2 and add the result to the sales date. Therefore, sales that took place in 2020 have an expiry year of 2022.