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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | MINDATE(eventDates) |
Parameter: New column name | earliestDate |
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | MAXDATE(eventDates) |
Parameter: New column name | latestDate |
Transformation Name |
|
|---|---|
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 |