What Are Aggregated Filters?

Aggregated filters provide a way to build queries that restrict results in the primary table by evaluating an aggregated value derived from one or more records in a foreign table that has a one-to-many relation to the primary table. Think of how financial institution customers often have more than one account, and we want to restrict a query about those customers based on some combination of values derived from all of their accounts--not just one. For example, if a user wants a list of customers whose total balance across all accounts is higher than a certain amount, this can be built using an aggregated filter off of the sum of the CurrentBalance field for all the Account records linked to that customer.

Aggregated filters are a powerful tool for exploring the relational dimensions of your data mart. Without aggregations, we would be restricted to conditions that evaluate each field in a related table individually (for example, "Give me a list of customers with checking accounts whose balance is over $50,000"). With aggregations, we can combine all the values in a set of records in different ways (depending on the type of aggregation method used) to create a single value that we can evaluate our condition against. For example, we can sum the CurrentBalance field for query that looks at all of that customer's accounts so that we capture records where a customer may have a number of different accounts, none of which has an individual balance high enough to meet our condition, but which when aggregated, does.

This article explains how aggregations work, what the different types of aggregations are, and how to build them using the Daybreak Query Wizard.

Types of Aggregations

Aggregations can be formed from the records in any table related to the primary table you are querying in a one-to-many fashion. For example, the Customer table has a one-to-many relationship to the Account table, because financial institution customers can have more than one account at the same time. By contrast, some tables have a one-to-one relation, for example, Transaction to Account, since every transaction record is recorded for just one account (in cases where one member transacts with another member in your dataset, there will be two transaction records, one, a debit linked to the initiating member's account and the other, a credit linked to the recipient customer). It is worth remembering that the type of relationship is also directionally relative: a relation from one table to another can be one to many (e.g. Customer to Transaction) whereas the relationship is one-to-one in the other direction (e.g. Transaction to Customer).

The importance of the one-to-many relationship is critical to understanding what aggregators do when they are used to create a conditional filter. In these filters, the values contained multiple records of a secondary table are combined in a certain way to yield a value that can be used to restrict the records that are selected from the primary table. There are various methods for performing these aggregations:

• Min - The lowest value in any of a field in the linked records in the secondary table.
• Max - The highest value in any of a field in the linked records in the secondary table.
• Sum - The sum of all values in a field in the linked records in the secondary table.
• Average - The average (mean) of a field in the linked records found in the secondary table.
• Count - The number of linked records linked (no field value can be specified)
• Count Distinct - The number of unique values found in a field in the linked records in the secondary table.

To give a concrete example, let's look at an example of records in the Account table's CurrentBalance field for a specific Customer who has five different deposit accounts:

AccountID	CurrentBalance
1GZFN	$1,307.23
TME65	$4,082.15
9ITOZ	$0.00
Y0VDB	$578.92
FBHRS	$0.00

Based on these five linked records, the results of the six aggregation methods would be as follows:

• Min - $0.00 (The lowest account value is zero)
• Max - $4,082.15 (The balance of this customer's highest balance account)
• Sum - $5,968.30 (The total of all account balances combined)
• Average - $1,193.66 (Note that zero or Null values are ignored)
• Count - 5 (there are five records)
• Count Distinct - 4 (because two of this customer's accounts have the same value, $0.00)

Creating an Aggregated Filter

To create an aggregated filter, first log in to the Daybreak app or click the Data Builder icon on the blue menu bar at the left of the screen if you are already logged into the app.

Next, create a new query using the query wizard by clicking the "Query Wizard" icon at the top of the menu area.

From the Query Wizard select Customers (or any other table with a one-to-many relationship to other tables), and then add your first condition by clicking "Add condition" below.

For our aggregation, let's query a related table with a one-to-many relationship from our primary table. To do this, select the "Account_CustomerKey" field from the drop down and choose "Includes all of" to combine all of the filter conditions.

First, let's add a simple condition to limit what gets aggregated to only records that match the savings account type. Select Product Type from the field drop down, and then the equals operator, and choose the "Savings" as the input to evaluate.

Now let's add an aggregated filter by clicking "Add aggregation." In the resulting template, specify the settings for the aggregation in this way: - Select "Sum" (so we can filter the sum of all savings accounts) - Select the field AverageBalance_YTD

For the condition to be applied to the aggregation, specify these settings: - Select the Greater than operator. - Enter "10,000" as the input value.

Now that the query is complete, click "Run" and in the Column selector dialogue, select the columns for Customer ID from the Customer table and scroll down to the Account table and select AverageBalance_YTD. When you've selected both fields as the columns for your output, click "Run" again to see the results.

In the results, we see that there are results for each account where the aggregated sum of all savings accounts with the ProductType of Savings adds up to more than $10,000.

In the results, we can see multiple records with the same customer ID. This happens when one customer has more than one savings account. In this dataset, we see that customer CT5LD has two savings accounts, one with an average YTD balance of $9,107.73 and another with $6,030.06. If we were to just look for accounts with AverageBalance_YTD greater than $10,000, neither of these records appear. They only appear because the combined (or aggregated) balance of both of this customer's savings accounts totals $15,137.79, which is greater than $10,000.

As this example shows, aggregations only work when a user selects a foreign table condition where there is a one-to-many relationship, such as Customer to Account or Account to TransactionSummary