Contents:
This section provides simple examples for how to use the ANYIF and LISTIF functions. These functions include the following:
ANYIF- Identifies a single value from a group that meets a specific condition. See ANYIF Function.LISTAIF- Lists all values within a group that meet a specified condition. See LISTIF Function.
Source:
The following data identifies sales figures by salespeople for a week:
| EmployeeId | Date | Sales |
|---|---|---|
| S001 | 1/23/17 | 25 |
| S002 | 1/23/17 | 40 |
| S003 | 1/23/17 | 48 |
| S001 | 1/24/17 | 81 |
| S002 | 1/24/17 | 11 |
| S003 | 1/24/17 | 25 |
| S001 | 1/25/17 | 9 |
| S002 | 1/25/17 | 40 |
| S003 | 1/25/17 | |
| S001 | 1/26/17 | 77 |
| S002 | 1/26/17 | 83 |
| S003 | 1/26/17 | |
| S001 | 1/27/17 | 17 |
| S002 | 1/27/17 | 71 |
| S003 | 1/27/17 | 29 |
| S001 | 1/28/17 | |
| S002 | 1/28/17 | |
| S003 | 1/28/17 | 14 |
| S001 | 1/29/17 | 2 |
| S002 | 1/29/17 | 7 |
| S003 | 1/29/17 | 99 |
Transform:
In this example, you are interested in the high performers. A good day in sales is one in which an individual sells more than 80 units. First, you want to identify the day of week:
derive type:single value:WEEKDAY(Date) as:'DayOfWeek'
Values greater than 5 inDayOfWeek are weekend dates. You can use the following to identify if anyone reached this highwater marker during the workweek (non-weekend):
pivot value:ANYIF(Sales, (Sales > 80 && DayOfWeek < 6)) group:EmployeeId,Date limit:1
Before adding the step to the recipe, you take note of the individuals who reached this mark in theanyif_Sales column for special recognition.Now, you want to find out sales for individuals during the week. You can use the following to filter the data to show only for weekdays:
pivot value:LISTIF(Sales, 1000, (DayOfWeek < 6)) group:EmployeeId,Date limit:1
To clean up, you might select and replace the following values in the listif_Sales column with empty strings:[" "] []
Results:
| EmployeeId | Date | listif_Sales |
|---|---|---|
| S001 | 1/23/17 | 25 |
| S002 | 1/23/17 | 40 |
| S003 | 1/23/17 | 48 |
| S001 | 1/24/17 | 81 |
| S002 | 1/24/17 | 11 |
| S003 | 1/24/17 | 25 |
| S001 | 1/25/17 | 40 |
| S002 | 1/25/17 | |
| S003 | 1/25/17 | 66 |
| S001 | 1/26/17 | 77 |
| S002 | 1/26/17 | 83 |
| S003 | 1/26/17 | |
| S001 | 1/27/17 | 17 |
| S002 | 1/27/17 | 71 |
| S003 | 1/27/17 | 29 |
| S001 | 1/28/17 | |
| S002 | 1/28/17 | |
| S003 | 1/28/17 | |
| S001 | 1/29/17 | |
| S002 | 1/29/17 | |
| S003 | 1/29/17 |
This page has no comments.