Page tree

NOTE:  Trifacta Wrangler is a free product with limitations on its features. Some features in the documentation do not apply to this product edition. See Product Limitations.

   

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

Function:

ItemDescription
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:

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

 

See Also for EXAMPLE - SUMIF Function:

 

This page has no comments.