yearend - 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 between 2020 and 2022 is loaded into a table called ‘Transactions’.

• The date field has been provided in the DateFormat system variable (MM/DD/YYYY) format.

• A preceding load statement which contains the following:

  • yearend() function which is set as the year_end field.

  • Timestamp() function which is set as the year_end_timestamp field.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
       *, 
       yearend(date) as year_end, 
       timestamp(yearend(date)) as year_end_timestamp 
       ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,01/13/2020,37.23 
8189,02/26/2020,17.17 
8190,03/27/2020,88.27 
8191,04/16/2020,57.42 
8192,05/21/2020,53.80 
8193,08/14/2020,82.06 
8194,10/07/2020,40.39 
8195,12/05/2020,87.21 
8196,01/22/2021,95.93 
8197,02/03/2021,45.89 
8198,03/17/2021,36.23 
8199,04/23/2021,25.66 
8200,05/04/2021,82.77 
8201,06/30/2021,69.98 
8202,07/26/2021,76.11 
8203,12/27/2021,25.12 
8204,06/06/2022,46.23 
8205,07/18/2022,84.21 
8206,11/14/2022,96.24 
8207,12/12/2022,67.67 
]; 

Results

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

• id

• date

• year_end

• year_end_timestamp

The ‘year_end’ field is created in the preceding load statement by using the yearend() function and passing the date field as the function’s argument.

The yearend() function initially identifies which year the date value falls into and returns a timestamp for the last millisecond of that year.

Transaction 8199 took place on April 23, 2021. The yearend() function returns the last millisecond of that year, which is December 31 at 11:59:59 PM.

Overview

The same dataset and scenario as the first example are used.

However, in this example, the task is to create a field, ‘previous_year_end’ , that returns the end date timestamp of the year prior to the year in which a transaction took place.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
       *, 
       yearend(date,-1) as previous_year_end, 
       timestamp(yearend(date,-1)) as previous_year_end_timestamp 
       ; 
Load 
* 
Inline  
[
id,date,amount 
8188,01/13/2020,37.23 
8189,02/26/2020,17.17 
8190,03/27/2020,88.27 
8191,04/16/2020,57.42 
8192,05/21/2020,53.80 
8193,08/14/2020,82.06 
8194,10/07/2020,40.39 
8195,12/05/2020,87.21 
8196,01/22/2021,95.93 
8197,02/03/2021,45.89 
8198,03/17/2021,36.23 
8199,04/23/2021,25.66 
8200,05/04/2021,82.77 
8201,06/30/2021,69.98 
8202,07/26/2021,76.11 
8203,12/27/2021,25.12 
8204,06/06/2022,46.23 
8205,07/18/2022,84.21 
8206,11/14/2022,96.24 
8207,12/12/2022,67.67 
]; 

Results

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

• id

• date

• previous_year_end

• previous_ year_end_timestamp

Because a period_no of -1 was used as the offset argument in the yearend() function, the function first identifies the year that the transactions take place in. It then looks one year prior and identifies the last millisecond of that year.

Transaction 8199 takes place on April 23, 2021. The yearend() function returns the last millisecond of the prior year, December 31, 2020 at 11:59:59 PM, for the ‘previous_year_end’ field.

Overview

The same dataset and scenario as the first example are used.

However, in this example, the company policy is for the year to begin from April 1.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
       *, 
       yearend(date,0,4) as year_end, 
       timestamp(yearend(date,0,4)) as year_end_timestamp 
       ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,01/13/2020,37.23 
8189,02/26/2020,17.17 
8190,03/27/2020,88.27 
8191,04/16/2020,57.42 
8192,05/21/2020,53.80 
8193,08/14/2020,82.06 
8194,10/07/2020,40.39 
8195,12/05/2020,87.21
8196,01/22/2021,95.93 
8197,02/03/2021,45.89 
8198,03/17/2021,36.23 
8199,04/23/2021,25.66
8200,05/04/2021,82.77 
8201,06/30/2021,69.98 
8202,07/26/2021,76.11 
8203,12/27/2021,25.12 
8204,06/06/2022,46.23 
8205,07/18/2022,84.21 
8206,11/14/2022,96.24 
8207,12/12/2022,67.67 
];  

Results

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

• id

• date

• year_end

• year_end_timestamp

Because the first_month_of_year argument of 4 is used in the yearend() function, it sets the first day of the year to April 1, and the last day of the year to March 31.

Transaction 8199 takes place on April 23, 2021. Because the yearend() function sets the start of the year to April 1, it returns March 31, 2022 as the ‘year_end’ value for the transaction.

Overview

The same dataset and scenario as the first example are used.

However, in this example, the dataset is unchanged and loaded into the application. The calculation that returns the end date timestamp of the year in which a transaction took place is created as a measure in a chart object of the application.

Load script

Transactions: 
Load 
* 
Inline  
[ 
id,date,amount 
8188,01/13/2020,37.23 
8189,02/26/2020,17.17 
8190,03/27/2020,88.27 
8191,04/16/2020,57.42 
8192,05/21/2020,53.80 
8193,08/14/2020,82.06 
8194,10/07/2020,40.39 
8195,12/05/2020,87.21 
8196,01/22/2021,95.93 
8197,02/03/2021,45.89 
8198,03/17/2021,36.23 
8199,04/23/2021,25.66 
8200,05/04/2021,82.77 
8201,06/30/2021,69.98 
8202,07/26/2021,76.11
8203,12/27/2021,25.12 
8204,06/06/2022,46.23 
8205,07/18/2022,84.21 
8206,11/14/2022,96.24 
8207,12/12/2022,67.67 
]; 

Results

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

• id

• date

To calculate in which year a transaction took place, create the following measures:

• =yearend(date)

• =timestamp(yearend(date))

The ‘end_of_year’ measure is created in the chart object by using the yearend() function and passing the date field as the function’s argument.

The yearend() function initially identifies which year the date value falls into returning a timestamp for the last millisecond of that year.

Transaction 8199 takes place on April 23, 2021. The yearend() function returns the last millisecond of that year, which is December 31 at 11:59:59 PM.

Overview

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

The load script contains:

• A dataset is loaded into a table called ‘Employee_Expenses’. The table contains the following fields:

  • employee IDs

  • employee name

  • average daily expense claims of each employee

The end user would like a chart object that displays, by employee id and employee name, the estimated expense claims still to be incurred for the remainder of the year. The financial year begins in January.

Load script

Employee_Expenses: 
Load 
* 
Inline  
[ 
employee_id,employee_name,avg_daily_claim 
182,Mark, $15 
183,Deryck, $12.5  
184,Dexter, $12.5 
185,Sydney,$27 
186,Agatha,$18 
]; 

Results

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

• employee_id

• employee_name

To calculate the projected expense claims, create the following measure:

=(yearend(today(1))-today(1))*avg_daily_claim

Set the measure’s Number Formatting to Money.

By using today’s date as its only argument, the yearend() function returns the end date of the current year. Then, by subtracting today’s date from the year end date, the expression returns the number of days remaining in this year.

This value is then multiplied by the average daily expense claim by each employee to calculate the estimated value of claims each employee is expected to make in the remaining year.