WEEKNUM Function

Contents:

The cloud-based product Trifacta Free is available now! Register for your free account.

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`

Examples

Tip: For additional examples, see Common Tasks.

Example - Day of Date functions

This example illustrates how you can apply functions to derive day-of-week values out of a column of Datetime type:

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

See Also:

Send Feedback

Page tree