“What was our average gift last year, what was the largest, and what did the spring appeal bring in?” Three simple numbers. In a spreadsheet you’d type =AVERAGE, =MAX and =SUM and be done in a minute. In Altru, getting the same answers depends on where you ask the question, and the wrong choice gives you a number that looks right but isn’t.
Below is how aggregates actually work across Altru’s reporting tools, where each one fits, and the mistakes that most often produce bad totals.
Start with the grain, not the function
Before you pick SUM, MAX or AVERAGE, decide what one row in your result represents. This is the single biggest source of wrong numbers in Altru reporting.
- One row per transaction (a revenue-based query): SUM of amount gives total revenue, AVERAGE gives average gift per transaction, MAX gives the single largest payment.
- One row per constituent (a constituent-based query): you’re summarizing each person’s history, such as total given, largest gift, or most recent visit. An average across those rows is average giving per donor, which is a different number from average gift.
Board members and development staff often mean “per donor” when they say “average gift.” Ask which one they want before you build anything.
Aggregates inside an ad-hoc query
Altru’s ad-hoc query tool is where most aggregate work happens. When you start from one record type and reach into a related one that can have many rows (a constituent’s revenue, memberships, or sales), numeric and date fields in that related area can typically be used with a summary option such as sum, count, minimum, maximum or average. The exact options vary by field, so look at what the query designer offers for the specific field you need rather than assuming every one supports every function.
There are two different places you can use those summaries, and they do different things:
Summaries as filters
Use these to decide who appears in the results. Examples: constituents whose total revenue in a date range is at least a certain amount, or members whose most recent visit date is before a cutoff. The summary narrows the list; it doesn’t necessarily show up as a column.
Summaries as output columns
Use these to show the calculated value on each row, such as each donor’s total giving or largest single gift. This is where people get burned: if you also output a non-summarized field from the same related area, you can end up with one row per transaction again, and every summary repeats on each of those rows.
When you’re unsure how a query behaves, test it on a few constituents you know well. Pick a donor with three gifts, total them by hand, and confirm the query matches. That two-minute check catches most problems.
Smart fields for numbers you need every week
If you keep rebuilding the same aggregate (lifetime giving, largest gift, total membership revenue, number of visits), it may belong in a smart field instead. Smart fields calculate a value per record on a schedule and then behave like an ordinary field in queries and lists. That makes them faster to use and consistent across reports.
Two caveats. Smart field values are only as fresh as their last processing run, so a gift entered this morning may not appear until the next refresh. And the kinds of smart fields available depend on your Altru setup; if you don’t see one that fits, check with Blackbaud support before assuming it can’t be done.
When the standard reports already have the number
Many of Altru’s built-in revenue, sales and membership reports already include totals and subtotals. If the question is “what did we take in last month by program,” check the standard reports first. Building a query to recreate a report total is a common way to end up with two numbers that don’t match and a long afternoon figuring out why (usually date range, refunds, or which revenue types are included).
Taking the data out to a spreadsheet
For anything beyond a straightforward sum or maximum, such as averages by month, medians, comparisons between two years, or grouped breakdowns, the practical route is to export the query results and finish the math in Excel or Google Sheets. A pivot table on a transaction-level export answers most “average by X” questions in a few clicks.
Keep the export at transaction level when you can. It’s easy to summarize detail in a spreadsheet, and impossible to recover detail from a summary.
Common mistakes that skew results
- Averaging averages. If you average each month’s average gift, small months count as much as big ones. Sum the amounts and divide by the total count instead.
- Refunds and adjustments. Decide whether negative amounts belong in the total. Including them is usually right for net revenue and wrong for “largest gift.”
- Households vs. individuals. Totals can double count if both household and member records are in scope. Pick one level and stay there.
- MAX on dates. Maximum on a date field gives you the most recent date, which is useful for “last visit” or “last gift,” but make sure the date field is the one you think it is (transaction date vs. posted date, for example).
- Zero-dollar rows. Comp tickets or complimentary memberships pull averages down. Filter them out if the question is about paid activity.
Keeping aggregate reports current
Exporting the same query every Monday to refresh a spreadsheet gets old quickly, and the spreadsheet is stale the moment someone forgets. Our Data Bridge syncs Altru query results to Google Sheets, so your SUM, MAX and AVERAGE formulas can sit on top of data that updates without a manual export. Altru still holds the data; the sheet is just where the math and the charts live.
Quick answers
Can an Altru query show a grand total at the bottom like a spreadsheet?
Not in the way a spreadsheet footer works. Query summaries are calculated per row or used as filters. For a single grand total, check the standard reports or total the column after export.
Why does my donor’s total show up on several rows?
The query is still returning one row per related record, usually because another output field comes from the same one-to-many area. Remove that field or move the summary to a constituent-level query.
