quarterstart - 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_quarter, which returns a timestamp for the start of the quarter when the transactions took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        quarterstart(date) as start_of_quarter,
        timestamp(quarterstart(date)) as start_of_quarter_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_quarter

• start_of_quarter_timestamp

The start_of_quarter field is created in the preceding load statement by using the quarterstart() function and passing the date field as the function’s argument. The quarterstart() function initially identifies which quarter the date value falls into. It then returns a timestamp for the first millisecond of that quarter.

Transaction 8203 took place on August 8. The quarterstart() function identifies that the transaction took place in the third quarter, and returns the first millisecond of that quarter, which is July 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_quarter_start, that returns the timestamp for the start of the quarter before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        quarterstart(date,-1) as previous_quarter_start,
        timestamp(quarterstart(date,-1)) as previous_quarter_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_quarter_start

• previous_quarter_start_timestamp

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

Transaction 8203 took place on August 8. The quarterstart() function identifies that the quarter before the transaction took place was between April 1 and June 30. It then returns the first millisecond of that quarter, April 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, we need to set March 1 as the beginning of the financial year.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        quarterstart(date,0,3) as start_of_quarter,
        timestamp(quarterstart(date,0,3)) as start_of_quarter_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_quarter

• start_of_quarter_timestamp

In this instance, because the first_month_of_year argument of 3 is used in the quarterstart() function, the start of the year moves from January 1 to March 1.

Transaction 8203 took place on August 8. Because the beginning of the year is March 1, the quarters in the year occur between March-May, June-August, September-November and December-February. The quarterstart() function identifies that the transaction took place in the quarter between the start of June and of August and returns the first millisecond of that quarter, which is June 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 end of the quarter when the transactions took place is created as a measure in a chart object of the application.

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 this field as a dimension: date.

Add the following measures:

• =quarterstart(date)

• =timestamp(quarterstart(date))

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

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

Transaction 8203 took place on August 8. The quarterstart() function identifies that the transaction took place in the third quarter, and returns the first millisecond of that quarter. This returned value is July 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 beginning of the quarter, 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 quarter to date.

Load script

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 this measure to calculate the accumulated interest:

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

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

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

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