CNFS IQ - How to Query on Donors Based on Their Total Giving

This topic shows you how to create a list of donors based on the total amount of their gifts. The provided example demonstrates how to generate total giving data. After you understand the basic method, you can use a variety of filters get the exact information you desire.


Querying Total Giving Data

Example of a Query on Total Giving

Additional Considerations

-

Querying Total Giving Data

The Total Contributions column enables you to view total giving data.

It is important to understand that even though the Total Contributions column is listed in the Contributions group in the Column Picker panel, you cannot select the column directly as a Result column. When designing a query, you must select this column in the Query Conditions panel and apply it as a condition on another column whose values are aggregated by the SUM function.

Depending on the exact nature of your question, you can refine your results by applying additional filter criteria, such as a specific fund name, fund type, and a custom date range, to name a few.

-

Example of a Query on Total Giving

This basic example shows how to query donors based on the total amount of gifts. Examine it closely to understand the basic concept. The donor list you generate in the results set is determined by the conditions you apply (in the Query Conditions panel). You have the option to apply a variety of conditions to get the exact information you need to answer your question.

Scenario

Suppose you want to obtain a list of families whose total giving to all funds was $250 or more.

Question

Our question is as follows:

What are the names of families who contributed more than $250 to all funds?

Setup

  1. Select the information you need. In the Column Picker panel, select these columns:

  • Families > Last Name

  • Funds > Fund DUID

  • Contributions > Payment Amount

  1. Because you want the sum total of the donor's payments, apply the aggregate SUM function to the Payment Amount column. To do this, in the Result Columns panel, click  to the right Contributions Payment and select SUM from the menu (see next illustration).

IMPORTANT

To obtain the correct results, you must apply the SUM aggregate function to the Contributions Payment Amount column. When the query runs, the SUM aggregate function calculates the sum of the values in the Payment Amount column to arrive at each family’s total gift amount.

  1. You want to view only those donors whose total contributions are $250 or more. To do this, in the Query Conditions panel, select the Contributions > Total Contributions column and apply this filter: is greater than or equal to 250.

You query setup now looks like this:

Run the Query

To run the query, click .

The results set contains the names of nine families whose total contribution to all funds is greater than or equal to $250. The Contributions Payment Amount Sum column in the Query Results panel shows each family's total contribution amount.

-

Additional Considerations

To further refine the list of donors in the results set, simply include more or different filter conditions. For example:

        To view total contributions for a specific fund, in the Query Conditions panel, apply one of fund identifier conditions, such as Funds > Fund DUID is equal to or Funds Description starts with [enter value].

        To view total contribution amounts to tax-deductible funds only, in the Query Conditions panel, apply this condition: Funds > is Tax Deductible.

        To view total contributions during a given date range, in the Query Conditions panel, apply this condition: Contributions > Payment Date is between  00:00 and 00:00.

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.