Contents:
This example illustrates how you can apply conditionals to calculate minimum, maximum, and most common date values.
Functions:
| Item | Description |
|---|---|
| KTHLARGESTDATE Function |
Extracts the ranked Datetime value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs must be valid Datetime values.
|
| KTHLARGESTUNIQUEDATE Function |
Extracts the ranked unique Datetime value from the values in a column, where k=1 returns the maximum value. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.
|
| KTHLARGESTDATEIF Function |
Extracts the ranked Datetime value from the values in a column, where k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.
|
| KTHLARGESTUNIQUEDATEIF Function |
Extracts the ranked unique Datetime value from the values in a column, where k=1 returns the maximum value, when a specified condition is met. The value for k must be between 1 and 1000, inclusive. Inputs must be Datetime.
|
Source:
Here is some example transaction data:
| Date | Product | Units | UnitCost | OrderValue |
|---|---|---|---|---|
| 3/28/2020 | ProductA | 4 | 10.00 | 40.00 |
| 3/8/2020 | ProductB | 4 | 20.00 | 80.00 |
| 3/12/2020 | ProductC | 2 | 30.00 | 60.00 |
| 3/23/2020 | ProductA | 1 | 10.00 | 10.00 |
| 3/20/2020 | ProductB | 2 | 20.00 | 40.00 |
| 3/12/2020 | ProductC | 9 | 30.00 | 270.00 |
| 3/28/2020 | ProductA | 5 | 10.00 | 50.00 |
| 3/23/2020 | ProductB | 8 | 20.00 | 160.00 |
| 3/16/2020 | ProductC | 9 | 30.00 | 270.00 |
| 3/8/2020 | ProductA | 5 | 10.00 | 50.00 |
| 3/10/2020 | ProductB | 3 | 20.00 | 60.00 |
| 3/13/2020 | ProductC | 1 | 30.00 | 30.00 |
| 3/12/2020 | ProductA | 7 | 10.00 | 70.00 |
| 3/10/2020 | ProductB | 7 | 20.00 | 140.00 |
| 3/24/2020 | ProductC | 9 | 30.00 | 270.00 |
| 3/15/2020 | ProductA | 8 | 10.00 | 80.00 |
| 3/10/2020 | ProductB | 5 | 20.00 | 100.00 |
| 3/10/2020 | ProductC | 4 | 30.00 | 120.00 |
Transformation:
The following transformation computes the third highest date in the Date column:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | kthlargestdate(Date, 3) |
| Parameter: New column name | 'kthlargestdate' |
This transformation computes the third highest unique value in the Date column:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | kthlargestuniquedate(Date, 3) |
| Parameter: New column name | 'kthlargestuniquedate' |
Following transformation calculates the 3rd highest date value when the OrderValue > 200:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | kthlargestdateif(Date, 3, OrderValue > 200) |
| Parameter: New column name | 'kthlargestdateif' |
Following transformation calculates the 3rd highest unique date value when the OrderValue > 200:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | kthlargestuniquedateif(Date, 3, OrderValue > 200) |
| Parameter: New column name | 'kthlargestuniquedateif' |
Results:
| Date | Product | Units | UnitCost | OrderValue | kthlargestdate | kthlargestuniquedate | kthlargestdateif | kthlargestuniquedateif |
|---|---|---|---|---|---|---|---|---|
| 3/28/2020 | ProductA | 4 | 10.00 | 40.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/8/2020 | ProductB | 4 | 20.00 | 80.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/12/2020 | ProductC | 2 | 30.00 | 60.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/23/2020 | ProductA | 1 | 10.00 | 10.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/20/2020 | ProductB | 2 | 20.00 | 40.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/12/2020 | ProductC | 9 | 30.00 | 270.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/28/2020 | ProductA | 5 | 10.00 | 50.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/23/2020 | ProductB | 8 | 20.00 | 160.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/16/2020 | ProductC | 9 | 30.00 | 270.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/8/2020 | ProductA | 5 | 10.00 | 50.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/10/2020 | ProductB | 3 | 20.00 | 60.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/13/2020 | ProductC | 1 | 30.00 | 30.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/12/2020 | ProductA | 7 | 10.00 | 70.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/10/2020 | ProductB | 7 | 20.00 | 140.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/24/2020 | ProductC | 9 | 30.00 | 270.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/15/2020 | ProductA | 8 | 10.00 | 80.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/10/2020 | ProductB | 5 | 20.00 | 100.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
| 3/10/2020 | ProductC | 4 | 30.00 | 120.00 | 03-24-2020 | 03-23-2020 | 03-23-2020 | 03-23-2020 |
This page has no comments.