Page tree



Contents:

The cloud-based version of Trifacta Wrangler is now available! Read all about it, and register for your free account.

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:

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:

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:

EmployeeIdsumif_Sales
S00142
S002126
S003142

 

 

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 14 rates

This page has no comments.