lunarweekstart - 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_week, that returns a timestamp for the start of the lunar week in which the transactions took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        lunarweekstart(date) as start_of_week,
        timestamp(lunarweekstart(date)) as start_of_week_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_week

• start_of_week_timestamp

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

The lunarweekstart() function identifies the lunar week into which the date falls, returning a timestamp for the first millisecond of that week.

Transaction 8189 took place on January 19. The lunarweekstart() function identifies that the lunar week begins on January 15. Therefore, the start_of_week value for that transaction returns the first millisecond of that day, which is January 15 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_lunar_week_start, that returns the timestamp for the start of the lunar week before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        lunarweekstart(date,-1) as previous_lunar_week_start,
        timestamp(lunarweekstart(date,-1)) as previous_lunar_week_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

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

Transaction 8189 took place on January 19. The lunarweekstart() function identifies that the lunar week begins on January 15. Therefore, the previous lunar week began on January 8 at 12:00:00 AM; this is the value returned for the previous_lunar_week_start field.

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. In this example, we set lunar weeks to begin on January 5.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        lunarweekstart(date,0,4) as start_of_week,
        timestamp(lunarweekstart(date,0,4)) as start_of_week_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_week

• start_of_week_timestamp

In this instance, because the first_week_date argument of 4 is used in the lunarweekstart() function, it offsets the start of the year from January 1 to January 5.

Transaction 8189 took place on January 19. Due to lunar weeks beginning on January 5, the lunarweekstart() function identifies that the lunar week containing January 19 begins on January 19 at 12:00:00 AM as well. Therefore, that is the value returned for the start_of_week field.

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 lunar week in which 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:

=lunarweekstart(date)

=timestamp(lunarweekstart(date))

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

The lunarweekstart() function identifies which lunar week the date value falls into, returning a timestamp for the last millisecond of that week.

Transaction 8189 took place on January 19. The lunarweekstart() function identifies that the lunar week begins on January 15. Therefore, the start_of_week value for that transaction is first millisecond of that day, which is the January 15 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 week, 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 week 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.

2. Add the following fields as dimensions:

   • loan_id

   • start_balance

3. Next, create the following measure to calculate the accumulated interest:

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

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

The lunarweekstart() 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 week.

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