Skip to main content

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