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

Load script

SET FirstWeekDay=6;

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

The weekstart() function initially identifies which week the date value falls into, returning a timestamp for the first 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 weekstart() function identifies that the first Sunday before February 5 – and therefore the start of the week – was on January 30, Therefore, the start_of_week value for that transaction returns the first millisecond of that day, which is January 30 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_week_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
        *,
        weekstart(date,-1) as previous_week_start,
        timestamp(weekstart(date,-1)) as previous_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

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

• date

• previous_week_start

• previous_week_start_timestamp

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

Transaction 8196 took place on June 15. The weekstart() function identifies that the week begins on June 12. Therefore, the previous week began on June 5 at 12:00:00 AM; this is the value that is returned for the previous_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. 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
        *,
        weekstart(date,0,1) as start_of_week,
        timestamp(weekstart(date,0,1)) 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 1 is used in the weekstart() function, it sets the first day of the week to Tuesday.

Transaction 8191 took place on February 5. The weekstart() function identifies that the first Tuesday before the this date – and therefore the start of the week and value returned – was 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 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 in which a transaction takes place, add the following measures:

• =weekstart(date)

• =timestamp(weekstart(date))

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

The weekstart() function initially identifies which week the date value falls into, returning a timestamp for the first 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 weekstart() function identifies that the first Sunday before February 5 – and therefore the start of the week – was January 30. Therefore, the start_of_week value for that transaction returns the first millisecond of that day, which is January 30 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 which is loaded into a table called Payroll.

• Data consisting of employee IDs, employee names, and the daily wage earned by each employee.

Employees begin work on Monday and work six days per week. The FirstWeekDay system variable must not be modified.

The end user would like a chart object that displays, by employee ID and employee name, the wages earned in the week to date.

Load script

Payroll: 
Load
*
Inline 
[
employee_id,employee_name,day_rate
182,Mark, $150
183,Deryck, $125
184,Dexter, $125
185,Sydney,$270
186,Agatha,$128
];

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 wages earned in the week to date:

   =if(today(1)-weekstart(today(1),0,0)<7,(today(1)-weekstart(today(1),0,0))*day_rate,day_rate*6)

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

The weekstart() function, by using today’s date as its first argument and 0 as its third argument, sets Monday as the first day of the week and returns the start date of the current week. By subtracting that result from the current date, the expression then returns the number of days that have elapsed so far this week.

The condition then evaluates whether there have been more than six days this week. If so, the employee’s day_rate is multiplied by 6 days. Otherwise, the day_rate is multiplied by the number of days that have occurred so far this week.