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 | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | mindateif(Date, OrderValue > 100) |
Parameter: New column name | 'Answers' |
Results: Value in Answers column: 3/10/2020
Question 2: What is the latest date when a $200.00 transaction occurred?
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | maxdateif(Date, OrderValue > 200) |
Parameter: New column name | 'Answer' |
Results: Value in Answers column: 3/24/2020
Question 3: On what date did the most transactions occur this month?
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | modedateif(Date, OrderValue > 0) |
Parameter: New column name | 'Answer' |
Results: Value in Answers column: 3/10/2020