This example shows how you can use the following functions to perform some analysis on Datetime columns.
MINDATE - Calculates the earliest (minimum) date from a column of Datetime column values. See MINDATE Function.MAXDATE - Calculates the latest (maximum) date from a column of Datetime column values. See MAXDATE Function.MODEDATE - Calculates the most frequent (mode) date from a column of Datetime column values. See MODEDATE Function.
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:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | unpivot col: Date1,Date2,Date3 groupEvery: 1 |
|---|
| p01Name | Columns |
|---|
| p01Value | Date1,Date2,Date3 |
|---|
| p02Name | Group size |
|---|
| p02Value | 1 |
|---|
| SearchTerm | Unpivot columns |
|---|
|
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:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | eventDates |
|---|
| Type | step |
|---|
| WrangleText | rename type: manual mapping: [value,'eventDates'] |
|---|
| p01Name | Option |
|---|
| p01Value | Manual rename |
|---|
| p02Name | Column |
|---|
| p02Value | value |
|---|
| p03Name | New column name |
|---|
| SearchTerm | Rename columns |
|---|
|
Using the following transformations, you can create new columns containing the min, max, and mode values for the Datetime values in eventDates:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | earliestDate |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: MINDATE(eventDates) as: 'earliestDate' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MINDATE(eventDates) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | latestDate |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: MAXDATE(eventDates) as: 'latestDate' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MAXDATE(eventDates) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | mostFrequentDate |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: MODEDATE(eventDates) as: 'mostFrequentDate' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | MODEDATE(eventDates) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
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 |
