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

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        lunarweekend(date) as end_of_week,
        timestamp(lunarweekend(date)) as end_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

• end_of_week

• end_of_week_timestamp

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

The lunarweekend() 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 lunarweekend() function identifies that the lunar week begins on January 15. Therefore, the end_of_week value for that transaction returns the last millisecond of the lunar week, which is January 21 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:

• The same dataset and scenario as the first example.

• The creation of a field, previous_lunar_week_end, that returns the timestamp for the end of the lunar week before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY';

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

• date

• previous_lunar_week_end

• previous_lunar_week_end_timestamp

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

Transaction 8189 took place on January 19. The lunarweekend() function identifies that the lunar week begins on January 15. Therefore, the previous lunar week began on the January 8 and ended on January 14 at 11:59:59 PM; this is the value that is returned for the previous_lunar_week_end 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
        *,
        lunarweekend(date,0,4) as end_of_week,
timestamp(lunarweekend(date,0,4)) as end_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

• end_of_week

• end_of_week_timestamp

In this instance, because the first_week_date argument of 4 is used in the lunarweekend() 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 lunarweekend() function identifies that the lunar week containing January 19 also begins on January 19. Therefore, the end of that lunar week occurs on January 25 at 11:59:59 PM; this is the value returned for the end_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 end 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:

=lunarweekend(date)

=timestamp(lunarweekend(date))

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

The lunarweekend() 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 lunarweekend() function identifies that the lunar week begins on January 15. Therefore, the end_of_week value for that transaction returns the last millisecond of the lunar week, which is January 21 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 which is loaded into a table called Employee_Expenses.

• The employee IDs, employee name and the 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 lunar week.

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

Do the following:

1. Load the data and open a sheet. Create a new table.

2. Add the following fields as dimensions:

   • employee_id

   • employee_name

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

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

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

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

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