##### Page tree

Contents:

NOTE:  Designer Cloud Powered by Trifacta Educational is a free product with limitations on its features. Some features in the documentation do not apply to this product edition. See Product Limitations.

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

Functions:

ItemDescription
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:

classIdDate1Date2Date3
c0012020-03-092020-03-132020-03-17
c0022020-03-092020-03-062020-03-21
c0032020-03-092020-03-162020-03-23
c0042020-03-092020-03-232020-04-06
c0052020-03-092020-04-092020-05-09
c0062020-03-092020-08-092021-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:

classIdkeyvalue
Same as previous.DateX column identifierCorresponding 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:

classIdkeyeventDatesmostFrequentDatelatestDateearliestDate
c001Date12020-03-092020-03-092021-01-092020-03-06
c001Date22020-03-132020-03-092021-01-092020-03-06
c001Date32020-03-172020-03-092021-01-092020-03-06
c002Date12020-03-092020-03-092021-01-092020-03-06
c002Date22020-03-062020-03-092021-01-092020-03-06
c002Date32020-03-212020-03-092021-01-092020-03-06
c003Date12020-03-092020-03-092021-01-092020-03-06
c003Date22020-03-162020-03-092021-01-092020-03-06
c003Date32020-03-232020-03-092021-01-092020-03-06
c004Date12020-03-092020-03-092021-01-092020-03-06
c004Date22020-03-232020-03-092021-01-092020-03-06
c004Date32020-04-062020-03-092021-01-092020-03-06
c005Date12020-03-092020-03-092021-01-092020-03-06
c005Date22020-04-092020-03-092021-01-092020-03-06
c005Date32020-05-092020-03-092021-01-092020-03-06
c006Date12020-03-092020-03-092021-01-092020-03-06
c006Date22020-08-092020-03-092021-01-092020-03-06
c006Date32021-01-092020-03-092021-01-092020-03-06

See Also for EXAMPLE - Date Functions - Min Max and Mode:

• Page:
• Page:
• Page: