The SUMIF
function can be used to sum the values in a column based on a condition and organized by group. See SUMIF 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:
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:
derive type:single value:WEEKDAY(Date) as:'DayOfWeek'
First you wish to examine weekday sales, whenDayOfWeek < 6
.
For each day of the week, you can preview the following aggregation:
pivot value:SUMIF(Sales, (DayOfWeek < 6)) group:EmployeeId
Performance is listed in the following order: S001, S002, S003To analyze the weekend, you change the above to the following:
pivot value:SUMIF(Sales, (DayOfWeek >= 5)) group:EmployeeId
Results:The following are the results for the weekend:
EmployeeId | sumif_Sales |
---|---|
S001 | 42 |
S002 | 126 |
S003 | 142 |
This page has no comments.