2019: Week 34 Solution
You can view our full solution workflow below and download it here!
For the week number we simply pull out the first character, i.e. the week number, using LEFT() and then convert this straight to a number using the INT() function. For weekday we instead use the SPLIT() function to extract the string of characters between the 2nd and 3rd spaces, i.e. the second word in the string.
DATENAME() extracts the name of the datepart as string whereas DATEPART() returns the integer value for the datepart.
First duplicate the data using an empty clean step. Then join the data back to itself on the following 3 conditions:
• Weekday = Weekday
• Month = Month
• Day of Month >= Day of Month
This calculation means that, for example, the 2nd Tuesday in each month now has two rows and the 4th Wednesday in each month now has 4 rows. If the data was across more than one year we’d also need to calculate and join on Year = Year.
To complete the running total, we use an aggregate step with the following settings:
• GROUP on [Month]
• GROUP on [Weekday]
• GROUP on [Date]
• SUM up [1] (or [Number of Rows]) and rename as [Week Number in Month].
This gives us a nice concise data set where each weekday in each month is numbered by its instance within the month.
• [Weekday] = [Weekday]
• [Week Number in Month] = [Week Number in Month]
After removing all the unnecessary duplicate fields we’re done.
 |
| Our full solution workflow. |
1. Prepare the Delivery Schedule data for joining.
We need to get two bits of data out of the [Delivery Schedule] field. Due to the consistent format of this field, we used the following two calculations to extract the week number in the month and the weekday:|
[Week Number in
Month]
|
[Weekday]
|
|
INT(
LEFT( [Delivery Schedule], 1)
)
|
SPLIT( [Delivery
Schedule], “ “, 2)
|
For the week number we simply pull out the first character, i.e. the week number, using LEFT() and then convert this straight to a number using the INT() function. For weekday we instead use the SPLIT() function to extract the string of characters between the 2nd and 3rd spaces, i.e. the second word in the string.
2. Get the dateparts from the Date Scaffold data.
The first step in preparing the Date Scaffold data is to extract the month, day of month, and weekday from the [Date] field. For this, we use a mix of the DATENAME() & DATEPART() fields:|
[Weekday]
|
[Month]
|
[Day of Month]
|
|
DATENAME(‘weekday’, [Date])
|
DATENAME(‘month’,
[Date])
|
DATEPART(‘day’,[Date])
|
DATENAME() extracts the name of the datepart as string whereas DATEPART() returns the integer value for the datepart.
3. Use a running total to number each instance of each weekday in each month.
We now have the weekdays and months, but we don’t have the number for each weekday within the month, e.g. the 2nd Tuesday, the 4th Wednesday, etc. To get around this we can calculate a running total using the [1] field. |
| The self-join for the running total. |
• Weekday = Weekday
• Month = Month
• Day of Month >= Day of Month
This calculation means that, for example, the 2nd Tuesday in each month now has two rows and the 4th Wednesday in each month now has 4 rows. If the data was across more than one year we’d also need to calculate and join on Year = Year.
To complete the running total, we use an aggregate step with the following settings:
• GROUP on [Month]
• GROUP on [Weekday]
• GROUP on [Date]
• SUM up [1] (or [Number of Rows]) and rename as [Week Number in Month].
This gives us a nice concise data set where each weekday in each month is numbered by its instance within the month.
 |
| The aggregation for the running total. |
4. Join all the data together.
Finally, we can now join our scaffold to our delivery schedule on:• [Weekday] = [Weekday]
• [Week Number in Month] = [Week Number in Month]
After removing all the unnecessary duplicate fields we’re done.
