Querying On Cumulative Amounts

There’s a ton of criteria you can use in queries to find the information you’re looking for. For example, have you ventured into the Cumulative Amounts category? Criteria in this category are extremely powerful tools that can help you locate groups of accounts or journal entries based on combined totals of particular criteria.

Let’s talk through a real world example of how this criteria can be useful and point out the important parts of the process as we go. I also recorded a short, no sound video that you can use to follow along.

Project: I want to find any donor that gave my organization $100 or more last year.
End Goal: I’m going to use this query to send a special acknowledgment to them via email. I want to let them know how their contributions have helped and how they can become more involved in my organization’s efforts, like upcoming events or volunteer opportunities.

Queries using cumulative criteria will always require at least 2 queries:

1. The first query is used to find the broad group of journal entries that you want to calculate.
2. The second query will use cumulative criteria, which is a calculator tool within queries.

Let's start building that first query! Forget about the cumulative criteria for now, just think about the transactions that you want to feed into the calculator. I want to find people that gave a certain amount, meaning I have received money from them, last year.

• Click Queries > Manage Queries
• Select a category that you want to store this query in
• Select New Query under the tasks menu
• Name the query Donations Last Year

I need just two pieces of criteria in this first query: Journal Entry Date and Individual Transaction Received. If you have more criteria, such as a certain Fund or Approach you only want to consider, you'd add that here as well. Again, we aren't thinking about how much we want them to have given, we just want to find the people that gave in the time frame we're looking for. I’ll click Save and View Queries to go back to my query category and part one of the process is done!



Now, let’s move on to the second query. This is where our cumulative criteria comes into play. We’re going to create a new query. I would suggest creating it in the same category where you created the first query to stay organized.

• Select New Query under the tasks menu
• Name the query Donors $100 Last Year

I'm going to set the starting query as the query I just built so that I have information to feed into my cumulative criteria. For the data return type, I'm choosing to return Journal Entries so that I can include the amount my organization received from them in a personalized email. I could also return Accounts here if I wanted to just see the people.



The only criteria I need in this query is from the Cumulative Amounts category: Cumulative Received. I don't want to put any other criteria into this query because this criteria works best by itself. That's why we included the date range in the first query. We can now click Save and Preview to see our results!



Keep in mind that the Preview will show each journal entry for the accounts that met the criteria, so you may see names more than once. I'm going to use this with a Communications template to send emails to these folks, but you could also run a report which will show the combined totals for each account.

Here are some main takeaways to remember when using any of the criteria under Cumulative Amounts:

1. Queries that use cumulative criteria will always require at least two queries.
2. Forget about the cumulative criteria in the starting query. Its goal is to locate the journal entries that you want to calculate, so it should have all the criteria for the group you want to find except for the cumulative one.
3. Your starting query should return only the journal entries you want to calculate. If you leave the criteria too wide, you'll end up with journal entries in your results that don't pertain to your end goal. For example, if I don't include the Individual Transaction Received criteria in my starting query, my end results may include Notes or Contacts from that date range. This is because the cumulative query will return all journal entries from the starting query for accounts that meet the criteria.
4. Cumulative criteria doesn't play well with others; don't try to mix it with other criteria or you will receive unintended results. Incorrectly placed cumulative criteria may also cause queries to run slowly.

Cumulative criteria is not limited to just calculating amounts, it can also help you find people that gave a certain number of times. I hope this was a helpful example of how to use this kind of criteria. Feel free to share how you've used cumulative critiera for a project in the comments below!