# EXAMPLE - Date Functions - Min Max and Mode

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 Date1,Date2,Date3 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 Manual rename value 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 Single row formula MINDATE(eventDates) earliestDate
 Transformation Name New formula Single row formula MAXDATE(eventDates) latestDate
 Transformation Name New formula Single row formula MODEDATE(eventDates) 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