weekend - 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 week when the transactions took place.

Load script

SET FirstWeekDay=6;

Transactions:
    Load
        *,
        weekend(date) as end_of_week,
        timestamp(weekend(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 weekend() function and passing the date field as the function’s argument.

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

Transaction 8191 took place on February 5. The FirstWeekDay system variable sets the first day of the week to a Sunday. The weekend() function identifies that the first Saturday after February 5– and therefore the end of the week – was on February 5. Therefore, the end_of_week value for that transaction returns the last millisecond of that day, which is February 5 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_week_end,that returns the timestamp for the start of the week before the transaction took place.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        weekend(date,-1) as previous_week_end,
        timestamp(weekend(date,-1)) as previous_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_week_end

• previous_week_end_timestamp

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

Transaction 8196 took place on June 15. The weekend() function identifies that the week begins on June 12. Therefore, the previous week ends on June 11 at 11:59:59 PM; this is the value returned for the previous_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. However, in this example, we need to set Tuesday as the first day of the work week.

Load script

SET DateFormat='MM/DD/YYYY';

Transactions:
    Load
        *,
        weekend(date,0,1) as end_of_week,
        timestamp(weekend(date,0,1)) 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 1 is used in the weekend() function, it sets the first day of the week to Tuesday.

Transaction 8191 took place on February 5. The weekend() function identifies that the first Monday after the this date – and therefore the end of the week and value returned – was on February 6 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. However, in this example, the unchanged dataset is loaded into the application. The calculation that returns a timestamp for the end of the week 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.

To calculate the start of the week that a transaction takes place in, add the following measures:

• =weekend(date)

• =timestamp(weekend(date))

The end_of_week measure is created in the chart object by using the weekend() function and passing the date field as the function’s argument. The weekend() function identifies which week the date value falls into, returning a timestamp for the last millisecond of that week.

Transaction 8191 took place on February 5. The FirstWeekDay system variable sets the first day of the week to a Sunday. The weekend() function identifies that the first Saturday after February 5 – and therefore the end of the week – was on February 5. Therefore, the end_of_week value for that transaction returns the last millisecond of that day, which is February 5 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.

• Data consisting of employee IDs, employee names, 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 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 and add these fields as dimensions:

   • employee_id

   • employee_name

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

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

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

The weekend() function, by using today’s date as its only argument, returns the end date of the current week. Then, by subtracting today’s date from the 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 that each employee is expected to make in the remaining week.