Date: Tue, 18 Jan 2022 01:02:19 +0000 (GMT) Message-ID: <975725071.105394.1642467739184@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_105393_701800350.1642467739184" ------=_Part_105393_701800350.1642467739184 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - SUMIF and COUNTDISTINCTIF Functions

EXAMPLE - SUMIF and COUNTDISTINCTIF Functions

This example illustrates how you can use conditional calculation fu= nctions.

Functions:

=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.
COUNTDISTINCTIF Function Generates the count of distinct non-null values= for rows in each group that meet a specific condition.

Source:

Here is some example polling data across 16 precincts in 8 cities across= 4 counties, where registrations have been invalidated at the polling stati= on, preventing voters from voting. Precincts where this issue has occurred = previously have been added to a watch list (precinctWatchList)= .

=20 =20 =20 =20 =20 =20 =20
totalReg invalidReg precinctWatchList precinctId cityId countyId
731 24 y 1 1 1
743 29 y 2 1 1
874 0   3 2 1
983 0   4 2 1
622 29   5 3 2
693 0   6 3 2
775 37 y 7 4 2
1025 49 y 8 4 2
787 13   9 5 3
342 0   10 5 3
342 39 y 11 6 3
387 28 y 12 6 3
582 59   13 7 4
244 0   14 7 4
940 6 y 15 8 4
901 4 y 16 8 4

Transformation:

First, you want to sum up the invalid registrations (invalidReg) for precincts that are already on the watchlist (precinctWatchL= ist =3D y). These sums are grouped by city, which can span mult= iple precincts:

=20
=20 =20 =20 =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 SUMIF(invalidReg, precinctWatchList =3D=3D= "y") cityId 'invalidRegbyCityId'
=20

The invalidRegbyCityId column contains invalid registration= s across the entire city.

Now, at the county level, you want to identify the number of precincts t= hat were on the watch list and were part of a city-wide registration proble= m.

In the following step, the number of cities in each count are counted wh= ere invalid registrations within a city is greater than 60.

• This step creates a pivot aggregation.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20
Transformation Name Pivot columns countyId COUNTDISTINCTIF(precinctId, invalidRegbyCi= tyId > 60) 1
=20

Results:

=20 =20 =20
countyId countdistinctif_precinctId
1 0
2 2
3 2
4 0

The voting officials in counties 2 and 3 should investigate their precin= ct registration issues.

=20
=20

=20

=20
• =20
=20 Page:= =20
=20
=20 SUMIF Function= =20
• =20
• =20
=20 Page:= =20
=20
• =20
=20

=20
=20
=20

------=_Part_105393_701800350.1642467739184--