Skip to main content

MODEDATE Function

Computes the most frequent (mode) value found in all row values in a Datetime column.

If a row contains a missing or null value, it is not factored into the calculation. If no Datetime values are found in the source column, the function returns a null value.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

modedate(myDates)

Output: Returns the most frequently appearing Datetime value from the myDates column.

Syntax and Arguments

modedate(function_col_ref)

Argument

Required?

Data Type

Description

function_col_ref

Y

string

Name of column to which to apply the function

For more information on syntax standards, see Language Documentation Syntax Notes.

function_col_ref

Name of the column the Datetime values of which you want to calculate the most frequent (mode) date.

  • Column must contain Datetime values.

  • Literal values are not supported as inputs.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

Datetime (column reference)

datTransactions

Examples

Astuce

For additional examples, see Common Tasks.

This example shows how you can apply statistical functions on Datetime columns.

Functions:

Item

Description

MINDATE Function

Computes the minimum value found in all row values in a Datetime column.

MAXDATE Function

Computes the maximum value found in all row values in a Datetime column.

MODEDATE Function

Computes the most frequent (mode) value found in all row values in a Datetime column.

Source:

The following dataset contains a set of three available dates for a set of classes:

classId

Date1

Date2

Date3

c001

2020-03-09

2020-03-13

2020-03-17

c002

2020-03-09

2020-03-06

2020-03-21

c003

2020-03-09

2020-03-16

2020-03-23

c004

2020-03-09

2020-03-23

2020-04-06

c005

2020-03-09

2020-04-09

2020-05-09

c006

2020-03-09

2020-08-09

2021-01-09

Transformation:

To compare dates across multiple columns, you must consolidate the values into a single column. You can use the following transformation to do so:

Transformation Name

Unpivot columns

Parameter: Columns

Date1,Date2,Date3

Parameter: Group size

1

The dataset is now contained in three columns, with descriptions listed below:

classId

key

value

Same as previous.

DateX column identifier

Corresponding value from the DateX column.

You can use the following to rename the value column to eventDates:

Transformation Name

Rename columns

Parameter: Option

Manual rename

Parameter: Column

value

Parameter: New column name

eventDates

Using the following transformations, you can create new columns containing the min, max, and mode values for the Datetime values in eventDates:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

MINDATE(eventDates)

Parameter: New column name

earliestDate

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

MAXDATE(eventDates)

Parameter: New column name

latestDate

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

MODEDATE(eventDates)

Parameter: New column name

mostFrequentDate

Results:

classId

key

eventDates

mostFrequentDate

latestDate

earliestDate

c001

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c001

Date2

2020-03-13

2020-03-09

2021-01-09

2020-03-06

c001

Date3

2020-03-17

2020-03-09

2021-01-09

2020-03-06

c002

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c002

Date2

2020-03-06

2020-03-09

2021-01-09

2020-03-06

c002

Date3

2020-03-21

2020-03-09

2021-01-09

2020-03-06

c003

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c003

Date2

2020-03-16

2020-03-09

2021-01-09

2020-03-06

c003

Date3

2020-03-23

2020-03-09

2021-01-09

2020-03-06

c004

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c004

Date2

2020-03-23

2020-03-09

2021-01-09

2020-03-06

c004

Date3

2020-04-06

2020-03-09

2021-01-09

2020-03-06

c005

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c005

Date2

2020-04-09

2020-03-09

2021-01-09

2020-03-06

c005

Date3

2020-05-09

2020-03-09

2021-01-09

2020-03-06

c006

Date1

2020-03-09

2020-03-09

2021-01-09

2020-03-06

c006

Date2

2020-08-09

2020-03-09

2021-01-09

2020-03-06

c006

Date3

2021-01-09

2020-03-09

2021-01-09

2020-03-06