monthend - 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 for 2022 that is loaded into a table called ‘Transactions’.

• A date field in theDateFormat system variable (MM/DD/YYYY) format.

• A preceding load statement that contains:

  • The monthend() function which is set as the ‘end_of_month’ field.

  • The timestamp function which is set as the ‘end_of_month_timestamp’ field.

Load script

SET DateFormat='MM/DD/YYYY'; 

Transactions: 
    Load 
    *, 
    monthend(date) as end_of_month, 
    timestamp(monthend(date)) as end_of_month_timestamp 
    ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/16/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

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

• id

• date

• end_of_month

• end_of_month_timestamp

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

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

Transaction 8192 took place on March 16. The monthend() function returns the last millisecond of that month, which is March 31 at 11:59:59 PM.

Overview

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

In this example, the task is to create a field, ‘previous_month_end’, that returns the timestamp for the end of the month before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY'; 
						
Transactions: 
    Load 
    *, 
    monthend(date,-1) as previous_month_end, 
    timestamp(monthend(date,-1)) as previous_month_end_timestamp 
    ; 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/16/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

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

• id

• date

• previous_month_end

• previous_month_end_timestamp

The monthend()function first identifies the month that the transactions take place in as aperiod_no of -1 is used as the offset argument. It then shifts one month prior and identifies the final millisecond of that month.

Transaction 8192 took place on March 16. The monthend() function identifies that the month before the transaction took place in was February. It then returns the final millisecond of that month, February 28 at 11:59:59 PM.

Overview

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

In this example, the dataset is unchanged and loaded into the app. The task is to create a calculation that returns a timestamp for the end of the month when the transactions took place as a measure in a chart of the app.

Load script

Transactions: 
Load 
* 
Inline  
[ 
id,date,amount 
8188,1/7/2022,17.17 
8189,1/19/2022,37.23 
8190,2/28/2022,88.27 
8191,2/5/2022,57.42 
8192,3/16/2022,53.80 
8193,4/1/2022,82.06 
8194,5/7/2022,40.39 
8195,5/16/2022,87.21 
8196,6/15/2022,95.93 
8197,6/26/2022,45.89 
8198,7/9/2022,36.23 
8199,7/22/2022,25.66 
8200,7/23/2022,82.77 
8201,7/27/2022,69.98 
8202,8/2/2022,76.11 
8203,8/8/2022,25.12 
8204,8/19/2022,46.23 
8205,9/26/2022,84.21 
8206,10/14/2022,96.24 
8207,10/29/2022,67.67 
]; 

Results

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

• date

• id

To calculate the end date of the month that a transaction takes place in, create the following measures:

• =monthend(date)

• =timestamp(monthend(date))

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

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

Transaction 8192 took place on March 16. The monthend() function returns the last millisecond of that month, which is March 31 at 11:59:59 PM.

Overview

In this example, a dataset is loaded into a table called ‘Employee_Expenses’. The table contains the following fields:

• Employee IDs

• Employee names

• The average daily expense claims of each employee.

The end user would like a chart that displays, by employee id and employee name, the estimated expense claim for the remainder of the month.

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 accumulated interest, create this measure:

=floor(monthend(today(1),0)-today(1))*avg_daily_claim

Set the measure’s Number formatting to Money.

The monthend()function returns the end date of the current month by using today’s date as its only argument. The expression returns the number of days that remain this month by subtracting today’s date from the month end date.

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 month.