Date: Sat, 3 Jun 2023 15:30:19 +0000 (UTC) Message-ID: <1923203004.12203.1685806219573@3978a672e405> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_12202_120459313.1685806219572" ------=_Part_12202_120459313.1685806219572 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - SUMIF Function

# EXAMPLE - SUMIF Function

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

Function:

=20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
SUMIF Function Generates the sum of rows in each group that me= et a specific condition.
WEEKDAY Function Derives the numeric value for the day of the we= ek (`1`, `2`, etc.). Input must be a reference to a c= olumn containing Datetime values.

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

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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `WEEKDAY(Date)` `'DayOfWeek'`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20
Transformation Name `Pivot columns` `groupId` `sumif(Sales, DayOfWeek < 6)`
=20

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

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Pivot columns` `groupId` `sumif(Sales, (DayOfWeek >=3D 5))`=
=20

Results:

The following are the results for the weekend:

EmployeeId sumif_Sales
S001 42
S002 126
S003 142

=20
=20

=20

=20
• =20
Page:<= /span>=20
=20
• =20
=20

=20
=20
=20

------=_Part_12202_120459313.1685806219572--