Page tree

Release 8.2.1



Contents:


Outdated release! Latest docs are Release 8.7: EXAMPLE - KTHLARGESTDATE Functions

   

This example illustrates how you can apply conditionals to calculate minimum, maximum, and most common date values:

  • KTHLARGESTDATE - Extracts the ranked Datetime value from the values in a column, where k=1 returns the maximum value. See KTHLARGESTDATE Function.
  • KTHLARGESTUNIQUEDATE - Extracts the unique ranked Datetime value from the values in a column, where k=1 returns the maximum value. See KTHLARGESTUNIQUEDATE Function.
  • KTHLARGESTDATEIF - Extracts the ranked Datetime value from the values in a column that meet a specified condition. See KTHLARGESTDATEIF Function.
  • KTHLARGESTUNIQUEDATEIF - Extracts the ranked unique Datetime value from the values in a column that meet a specified condition. See KTHLARGESTUNIQUEDATEIF Function.

Source:

Here is some example transaction data:

DateProductUnitsUnitCostOrderValue
3/28/2020ProductA410.0040.00
3/8/2020ProductB420.0080.00
3/12/2020ProductC230.0060.00
3/23/2020ProductA110.0010.00
3/20/2020ProductB220.0040.00
3/12/2020ProductC930.00270.00
3/28/2020ProductA510.0050.00
3/23/2020ProductB820.00160.00
3/16/2020ProductC930.00270.00
3/8/2020ProductA510.0050.00
3/10/2020ProductB320.0060.00
3/13/2020ProductC130.0030.00
3/12/2020ProductA710.0070.00
3/10/2020ProductB720.00140.00
3/24/2020ProductC930.00270.00
3/15/2020ProductA810.0080.00
3/10/2020ProductB520.00100.00
3/10/2020ProductC430.00120.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: 

DateProductUnitsUnitCostOrderValuekthlargestdatekthlargestuniquedatekthlargestdateifkthlargestuniquedateif
3/28/2020ProductA410.0040.0003-24-202003-23-202003-23-202003-23-2020
3/8/2020ProductB420.0080.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductC230.0060.0003-24-202003-23-202003-23-202003-23-2020
3/23/2020ProductA110.0010.0003-24-202003-23-202003-23-202003-23-2020
3/20/2020ProductB220.0040.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/28/2020ProductA510.0050.0003-24-202003-23-202003-23-202003-23-2020
3/23/2020ProductB820.00160.0003-24-202003-23-202003-23-202003-23-2020
3/16/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/8/2020ProductA510.0050.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB320.0060.0003-24-202003-23-202003-23-202003-23-2020
3/13/2020ProductC130.0030.0003-24-202003-23-202003-23-202003-23-2020
3/12/2020ProductA710.0070.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB720.00140.0003-24-202003-23-202003-23-202003-23-2020
3/24/2020ProductC930.00270.0003-24-202003-23-202003-23-202003-23-2020
3/15/2020ProductA810.0080.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductB520.00100.0003-24-202003-23-202003-23-202003-23-2020
3/10/2020ProductC430.00120.0003-24-202003-23-202003-23-202003-23-2020


This page has no comments.