Counting and aggregation determine how Impact Hub summarizes your data in reports. Choosing the right aggregation method is essential for producing accurate, trustworthy results—especially when working with datasets that include multiple forms or repeated records.
This article explains how aggregation works in Impact Hub and how to choose the correct option when building visuals, so your numbers are accurate and reliable.
Start with the result you expect
When a number looks too high or too low, ask one question first:
Am I counting unique records, or am I counting every row of data?
Impact Hub datasets can include multiple records per person or case, especially when Tier 2 forms (such as services or activities) are involved. Aggregation controls how those records are summarized in a visual.
Use metadata fields for reliable counts
Impact Hub automatically includes metadata fields in datasets to support accurate reporting. These fields are especially important when counting unique records.
Common metadata fields include:
DOCUMENT_ID – identifies individual form records
PARENT_ID – links Tier 2 records back to their parent record
ACTIVE – indicates whether a record is active
MOD_TIME – shows when a record was last updated
These fields help ensure counts reflect the structure of the data, not just the number of rows.
Choose the right aggregation method
Aggregation determines how Builder summarizes data in a visual. The choice you make directly affects the result.
Use Count Distinct when:
Counting people
Counting households
Counting cases
You expect each record to be counted only once
Using Count in these situations will inflate totals if multiple related records exist.
Use Count when:
Counting service sessions
Counting encounters or activities
Counting events where every row should be included
Use Sum when:
Adding numeric values such as hours or dollars
Use Average when:
Calculating a meaningful mean value
You understand how repeated records affect the result
If you’re unsure, start with Count Distinct for people and adjust only if the result doesn’t match the question you’re answering.
A reliable pattern for counting people or cases
When building a visual to count unique people or cases:
Place a category field (such as Program Name) in Group / Dimension.
Place a metadata ID field tied to the anchor form in Value.
Set the aggregation to Count Distinct.
This pattern avoids double-counting when datasets include multiple related records.
Watch out for repeated records
If your dataset includes Tier 2 forms (such as services or activities), you may see higher counts than expected.
Check whether:
The dataset includes Tier 2 forms
The anchor form represents the record you intend to count
The aggregation matches the record type
If numbers look unexpectedly high, it’s often a sign that the aggregation doesn’t match the reporting question.
Adjust and verify at the visual level
Aggregation is applied per visual, not globally.
When troubleshooting:
Adjust the aggregation for one visual at a time
Compare similar visuals to confirm consistency
Switch datasets if expected fields are missing
Correct aggregation should produce stable, explainable results when filters or groupings change.
Important things to know
Aggregation choices affect how every visual calculates totals.
Changing datasets can change how aggregations behave.
Removing or adding filters may change counts without changing aggregation.
There is no “one-size-fits-all” aggregation—only the one that fits your question.