makeweekdate - 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 weekly sales total for 2022 in a table called Sales.

• Transaction dates provided across three fields: year, week, and sales.

• A preceding load which is used to create a measure end_of_week, using the makeweekdate() function to return the date for the Friday of that week in the format MM/DD/YYYY.

To prove that the date returned is a Friday, the end_of_week expression is also wrapped in the weekday() function to show the day of the week.

Load script

SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;

Transactions:
    Load
        *,
        makeweekdate(transaction_year, transaction_week,4) as end_of_week,
        weekday(makeweekdate(transaction_year, transaction_week,4)) as week_day
        ; 
Load * Inline [
transaction_year, transaction_week, sales
2022, 01, 10000
2022, 02, 11250
2022, 03, 9830
2022, 04, 14010
2022, 05, 28402
2022, 06, 9992
2022, 07, 7292
];

Results

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

• transaction_year

• transaction_week

• end_of_week

• week_day

The end_of_week field is created in the preceding load statement by using the makeweekdate() function. The transaction_year, transaction_week fields are passed through the function as the year and week arguments. A value of 4 is used for the day argument.

The function then combines and converts these values into a date field, returning the results in the format of the DateFormat system variable.

The makeweekdate() function, and its arguments are also wrapped in a weekday() function to return the week_day field; and as can be seen in the table above, the week_day field shows that these dates do occur on a Friday.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset containing weekly sales totals for 2022 in a table called Sales.

• Transaction dates provided across three fields: year, week, and sales.

• A preceding load, which is used to create a measure, first_day_of_week, using the makeweekdate() function. This will return the date for the Monday of that week in the format MM/DD/YYYY.

To prove that the date returned is a Monday, the first_day_of_week expression is also wrapped in the weekday() function to show the day of the week.

Load script

SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;

Transactions:
    Load
        *,
        makeweekdate(transaction_year, transaction_week) as first_day_of_week,
        weekday(makeweekdate(transaction_year, transaction_week)) as week_day
        ; 
Load * Inline [
transaction_year, transaction_week, sales
2022, 01, 10000
2022, 02, 11250
2022, 03, 9830
2022, 04, 14010
2022, 05, 28402
2022, 06, 9992
2022, 07, 7292
];

Results

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

• transaction_year

• transaction_week

• first_day_of_week

• week_day

The first_day_of_week field is created in the preceding load statement by using the makeweekdate() function. The transaction_year and transaction_week parameters are passed as function arguments, and the day parameter is left blank.

The function then combines and converts these values into a date field, returning the results in the format of the DateFormat system variable.

The makeweekdate() function and its arguments are also wrapped in a weekday() function to return the week_day field. As can be seen in the table above, the week_day field returns Monday in all cases since that parameter was left blank in the makeweekdate() function, which defaults to 0 (first day of the week), and first day of the week is set to Monday by the FirstWeekDay system variable.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset containing weekly sales totals for 2022 in a table called Sales.

• Transaction dates provided across three fields: year, week, and sales.

In this example, a chart object will be used to create a measure equivalent to the end_of_week calculation from the first example. This measure will use the makeweekdate() function to return the date for the Friday of that week in the format MM/DD/YYYY.

To prove that the date returned is a Friday, a second measure is created to return the day of the week.

Load script

SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;

Master_Calendar:
Load * Inline [
transaction_year, transaction_week, sales
2022, 01, 10000
2022, 02, 11250
2022, 03, 9830
2022, 04, 14010
2022, 05, 28402
2022, 06, 9992
2022, 07, 7292
];

Results

Do the following:

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

   • transaction_year

   • transaction_week

2. To perform the calculation equivalent to that of the end_of_weekfield from the first example, create the following measure:

   =makeweekdate(transaction_year,transaction_week,4)

3. To calculate the day of the week for each transaction, create the following measure:

   =weekday(makeweekdate(transaction_year,transaction_week,4))

An equivalent field to end_of_week is created in the chart object as a measure by using the makeweekdate() function. The transaction_year and transaction_week fields are passed as year and week arguments. A value of 4 is used for the day argument.

The function then combines and converts these values into a date field, returning the results in the format of the DateFormat system variable.

The makeweekdate() function and its arguments are also wrapped in a weekday() function to return a calculation equivalent to that of the week_day field from the first example. As can be seen in the table above, the last column on the right shows that these dates do occur on a Friday.

Overview

In this example, create a list of dates containing all the Fridays for the year 2022.

Open the Data load editor and add the load script below to a new tab.

Load script

SET DateFormat='MM/DD/YYYY';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;

Calendar:
    load
	    *,
        weekday(date) as weekday
    where year(date)=2022;
load
    makeweekdate(2022,recno()-2,4) as date
AutoGenerate 60;

Results

The makeweekdate() function finds each Friday in 2022. Using a week parameter of -2 ensures that no dates are missed. Finally, a preceding load creates an additional weekday field for clarity, to show that each date value is a Friday.