monthstart - 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, which is loaded into a table called Transactions.

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

• The creation of a field, start_of_month, which returns a timestamp for the start of the month when the transactions took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        monthstart(date) as start_of_month,
        timestamp(monthstart(date)) as start_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:

• date

• start_of_month

• start_of_month_timestamp

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

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

Transaction 8192 took place on March 16. The monthstart() function returns the first millisecond of that month, which is March 1 at 12:00:00 AM.

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

• The creation of a field, previous_month_start, which returns the timestamp for the start of the month before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        monthstart(date,-1) as previous_month_start,
        timestamp(monthstart(date,-1)) as previous_month_start_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:

• date

• previous_month_start

• previous_month_start_timestamp

In this instance, because a period_no of -1 was used as the offset argument in the monthstart() function, the function first identifies the month that the transactions take place in. It then shifts one month prior and identifies the first millisecond of that month.

Transaction 8192 took place on March 16. The monthstart() function identifies that the month before the transaction took place in was February. It then returns the first millisecond of that month, February 1 at 12:00:00 AM.

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

However, in this example, the unchanged dataset is loaded into the application. The calculation that returns a timestamp for the start of the month when the transactions took place is created as a measure in a chart object of the application.

Load script

SET DateFormat='MM/DD/YYYY';
 
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 this field as a dimension: date.

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

• =monthstart(date)

• =timestamp(monthstart(date))

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

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

Transaction 8192 took place on March 16. The monthstart() function identifies that the transaction took place in March and returns the first millisecond of that month, which is March 1 at 12:00:00 AM.

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 loan balances, which is loaded into a table called Loans.

• Data consisting of loan IDs, the balance at the start of the month, and the simple interest rate charged on each loan per annum.

The end user would like a chart object that displays, by loan ID, the current interest that has been accrued on each loan in the month to date.

Load script

SET DateFormat='MM/DD/YYYY';

Loans:
Load
*
Inline
[
loan_id,start_balance,rate
8188,$10000.00,0.024
8189,$15000.00,0.057
8190,$17500.00,0.024
8191,$21000.00,0.034
8192,$90000.00,0.084
];

Results

Do the following:

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

   • loan_id

   • start_balance

2. Next, create a measure to calculate the accumulated interest:

   =start_balance*(rate*(today(1)-monthstart(today(1)))/365)

3. Set the measure's Number formatting to Money.

The monthstart() function, using today’s date as its only argument, returns the start date of the current month. By subtracting that result from the current date, the expression returns the number of days that have elapsed so far this month.

This value is then multiplied by the interest rate and divided by 365 to return the effective interest rate incurred for this period. The result is then multiplied by the starting balance of the loan to return the interest that has been accrued so far this month.