## Key

• This line was removed.
• Formatting was changed.
Excerpt

This example can be used to sum the values in a column based on a condition and organized by group.

Function:

D generate list excerpts
pages SUMIF Function,WEEKDAY Function

Source:

The following data identifies sales figures by salespeople for a week:

EmployeeIdDateSales
S0011/23/1725
S0021/23/1740
S0031/23/1748
S0011/24/1781
S0021/24/1711
S0031/24/1725
S0011/25/179
S0021/25/1740
S0031/25/17
S0011/26/1777
S0021/26/1783
S0031/26/17
S0011/27/1717
S0021/27/1771
S0031/27/1729
S0011/28/17
S0021/28/17
S0031/28/1714
S0011/29/172
S0021/29/177
S0031/29/1799

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:

D trans
RawWrangle true 'DayOfWeek' step derive type:single value:WEEKDAY(Date) as:'DayOfWeek' Formula type Single row formula Formula WEEKDAY(Date) New column name New formula

First you wish to examine weekday sales, when `DayOfWeek < 6`. For each day of the week, you can preview the following aggregation:

D trans
RawWrangle true step pivot group: EmployeeId value: sumif(Sales, DayOfWeek < 6) Row labels groupId Values sumif(Sales, DayOfWeek < 6) Pivot columns

Performance is listed in the following order: S001, S002, S003.

To analyze the weekend, you change the above to the following:

D trans
RawWrangle true step pivot value:SUMIF(Sales, (DayOfWeek >= 5)) group:EmployeeId Row labels groupId Values sumif(Sales, (DayOfWeek >= 5)) Pivot columns

Results:

The following are the results for the weekend:

EmployeeIdsumif_Sales
S00142
S002126
S003142

D s also
label SUMIF