Contents:
This example can be used to sum the values in a column based on a condition and organized by group.
Function:
| Item | Description |
|---|---|
| SUMIF Function | Generates the sum of rows in each group that meet a specific condition. |
| WEEKDAY Function |
Derives the numeric value for the day of the week (1, 2, etc.). Input must be a reference to a column containing Datetime values.
|
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 |
Transformation:
You want to know how your salespeople are doing by the day of the week. To the above, you add a column that identifies the day of the week:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | WEEKDAY(Date) |
| Parameter: New column name | 'DayOfWeek' |
First you wish to examine weekday sales, when DayOfWeek < 6. For each day of the week, you can preview the following aggregation:
| Transformation Name | Pivot columns |
|---|---|
| Parameter: Row labels | groupId |
| Parameter: Values | sumif(Sales, DayOfWeek < 6) |
Performance is listed in the following order: S001, S002, S003.
To analyze the weekend, you change the above to the following:
| Transformation Name | Pivot columns |
|---|---|
| Parameter: Row labels | groupId |
| Parameter: Values | sumif(Sales, (DayOfWeek >= 5)) |
Results:
The following are the results for the weekend:
| EmployeeId | sumif_Sales |
|---|---|
| S001 | 42 |
| S002 | 126 |
| S003 | 142 |
This page has no comments.