EXAMPLE

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:

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
p03Value	'DayOfWeek'
Type	step
WrangleText	derive type:single value:WEEKDAY(Date) as:'DayOfWeek'
p01Name	Formula type
p01Value	Single row formula
p02Name	Formula
p02Value	WEEKDAY(Date)
p03Name	New column name
SearchTerm	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
Type	step
WrangleText	pivot group: EmployeeId value: sumif(Sales, DayOfWeek < 6)
p01Name	Row labels
p01Value	groupId
p02Name	Values
p02Value	sumif(Sales, DayOfWeek < 6)
SearchTerm	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
Type	step
WrangleText	pivot value:SUMIF(Sales, (DayOfWeek >= 5)) group:EmployeeId
p01Name	Row labels
p01Value	groupId
p02Name	Values
p02Value	sumif(Sales, (DayOfWeek >= 5))
SearchTerm	Pivot columns

Results:

The following are the results for the weekend:

D s also

label	SUMIF

Versions Compared