EXAMPLE - KTHLARGESTDATE Functions
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 |
KTHLARGESTUNIQUEDATE Function | Extracts the ranked unique Datetime value from the values in a column, where |
KTHLARGESTDATEIF Function | Extracts the ranked Datetime value from the values in a column, where |
KTHLARGESTUNIQUEDATEIF Function | Extracts the ranked unique Datetime value from the values in a column, where |
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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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 |