Date: Tue, 11 May 2021 18:50:42 +0000 (GMT) Message-ID: <467403666.7047.1620759042891@6a789edf488b> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_7046_1515198229.1620759042891" ------=_Part_7046_1515198229.1620759042891 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 the following condition= al calculation functions to analyze polling data:

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 formulaParameter: Formula typeSingle row formulaParameter: FormulaSUMIF(invalidReg, precinctWatchList =3D=3D= "y")Parameter: Group rows bycityIdParameter: New column name'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 NamePivot columnsParameter: Row labelscountyIdParameter: ValuesCOUNTDISTINCTIF(precinctId, invalidRegbyCi= tyId > 60)Parameter: Max number of columns to create1 =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. ```
``` ------=_Part_7046_1515198229.1620759042891-- ```