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

Functions:

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:

This transformation computes the third highest unique value in the Date column:

Following transformation calculates the 3rd highest date value when the OrderValue > 200:

Following transformation calculates the 3rd highest unique date value when the OrderValue > 200:

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