Derives the numeric value for the week within the year (
1
, 2
, etc.). Input must be the output of the DATE
function or a reference to a column containing Datetime values. The output of this function increments on Sunday.Week 1 of the year is the week that contains January 1.
NOTE: If the source Datetime value does not include a valid input for this function, a missing value is returned.
Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Known Issue:
The current implementation of the WEEKNUM function returns a maximum value of 52. However, in some years, such as 2020, there is a 53rd week. You can add steps similar to the following to work around this issue:
derive type: single value: dateformat(date(year(myDate), 1, 1), 'yyyy\/MM\/dd') as: 'NewYearsDayforMyYear'
derive type: single value: if(datedif(NewYearsDayforMyYear, myDate, day) > (52 * 7), 53, weeknum(myDate)) as: 'weekNumforMyDate'
The above implementation differs from other platforms, such as Microsoft Excel.
Basic Usage
Column reference example:
weeknum(MyDate)
Output: Returns the numeric week number values derived from the MyDate
column.
Syntax and Arguments
weeknum(datetime_col)
Argument | Required? | Data Type | Description |
---|---|---|---|
datetime_col | Y | datetime | Name of column whose week number values are to be computed |
For more information on syntax standards, see Language Documentation Syntax Notes.
datetime_col
Name of the column whose week number value is to be computed.
- Missing values for this function in the source data result in missing values in the output.
- Multiple columns and wildcards are not supported.
Tip: You cannot insert constant Datetime values as inputs to this function. However, you can use the following: WEEKNUM(DATE(2017,12,20))
.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | Datetime | myDate |
Tip: For additional examples, see Common Tasks.Examples
Example - Day of Date functions
WEEKDAY
- returns numeric value for the day of the week for source Datetime values. See WEEKDAY Function.WEEKNUM
- returns the numeric value for the week within the year for source Datetime values. See WEEKNUM Function.DATEFORMAT
- can be used to format Datetime values in many different ways. See DATEFORMAT Function.
Source:
myDate |
---|
10/30/17 |
10/31/17 |
11/1/17 |
11/2/17 |
11/3/17 |
11/4/17 |
11/5/17 |
11/6/17 |
Transformation:
The following transformation step generates a numeric value for the day of week in a new column:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | WEEKDAY (myDate) |
Parameter: New column name | 'weekDayNum' |
The following step generates a full text version of the name of the day of the week:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DATEFORMAT(myDate, 'EEEE') |
Parameter: New column name | 'weekDayNameFull' |
The following step generates a three-letter abbreviation for the name of the day of the week:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DATEFORMAT(myDate, 'EEE') |
Parameter: New column name | 'weekDayNameShort' |
The following step generates the numeric value of the week within the year:
Transformation Name | New formula |
---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | WEEKNUM (myDate) |
Parameter: New column name | 'weekNum' |
Results:
myDate | weekDayNum | weekDayNameFull | weekDayNameShort | weekNum |
---|---|---|---|---|
10/30/17 | 1 | Monday | Mon | 44 |
10/31/17 | 2 | Tuesday | Tue | 44 |
11/1/17 | 3 | Wednesday | Wed | 44 |
11/2/17 | 4 | Thursday | Thu | 44 |
11/3/17 | 5 | Friday | Fri | 44 |
11/4/17 | 6 | Saturday | Sat | 44 |
11/5/17 | 7 | Sunday | Sun | 45 |
11/6/17 | 1 | Monday | Mon | 45 |
This page has no comments.