EXAMPLE - DATEIF Functions

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

Functions:

Item

Description

MINDATEIF Function

Returns the minimum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.

MAXDATEIF Function

Returns the maximum Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.

MODEDATEIF Function

Returns the most common Datetime value of rows in each group that meet a specific condition. Set of values must valid Datetime values.

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 and Results:

These functions are useful for asking questions about your data. In the following, you can review specific questions and see the results immediately.

Question 1: What is the earliest date when a $100.00 transaction occurred?  Transformation Name New formula Single row formula mindateif(Date, OrderValue > 100) 'Answers' Results: Value in Answers column: 3/10/2020 Question 2: What is the latest date when a$200.00 transaction occurred?

 Transformation Name New formula Single row formula maxdateif(Date, OrderValue > 200) 'Answer'

Results: Value in Answers column: 3/24/2020

Question 3: On what date did the most transactions occur this month?

 Transformation Name New formula Single row formula modedateif(Date, OrderValue > 0) 'Answer'

Results: Value in Answers column: 3/10/2020