Formula subquery filters limit query results by testing a formula against values available in the subquery. Use a Formula aggregate when a standard subquery filter aggregate, such as Exists, Count, Comparison, or Sum, cannot describe the condition by itself.
A formula subquery filter is useful when you need to search text patterns, compare calculated date intervals, convert values before comparing them, or combine multiple conditions in one filter expression.
💡 Tip
Use built-in aggregate functions for simple conditions. Use Formula when the filter requires custom logic that cannot be expressed with a standard aggregate.
Choosing the Formula aggregate
A subquery filter looks across the many side of a one-to-many relationship to determine which base records the query should return. In a formula subquery filter, you add exports inside the subquery and reference those exports in the formula with @export_name.
The formula should evaluate to true for records that should remain in the query results. For example, a formula can return person records where a last name begins with A through F, or application records where the number of days between two dates is less than a target value.
Creating the formula subquery filter
Open the query in the Edit Query view.
In the Filters section, select Subquery Filter.
Enter a descriptive Name for the filter.
For Aggregate, select Formula.
Add any joins needed to reach the related rows that the formula should evaluate.
Add any filters needed to narrow the related rows before the formula is evaluated.
Add one export for each value that the formula will reference.
Rename each export so it has no spaces or special characters, except underscores. Names must be unique within the formula.
Confirm that each export uses a format type that works with the formula. For example, use a numeric format such as Int, Real, or Money when the formula performs a mathematical comparison.
In the Formula box, enter the formula, then select Save.
Writing formula syntax
Reference each export by adding @ before the export name. For example, an export named person_last is referenced as @person_last.
Type @ in the Formula box to see suggested exports. Type # to see suggested functions.
Use SQL-style operators and functions in the formula. Common patterns include LIKE, try_convert, datediff, and nullif.
Checking the filter logic
Use Matching Rows to check how many records the subquery returns. If the count is much higher or lower than expected, review the joins, filters, exports, and formula syntax.
Select Check Logic and enter a sample record to see whether that record meets the subquery filter criteria. Checking a known matching record and a known nonmatching record can help confirm that the formula is returning the intended results.
Using common formula patterns
Filtering text with LIKE
Use LIKE to filter records based on text patterns. The percent sign (%) represents zero, one, or many characters.
@person_last LIKE '[A-F]%'Filtering by date difference
Use datediff when the filter needs to compare the amount of time between two dates. For example, the following formula returns records where an application was submitted within seven days of the application created date.
datediff(day, @application_created_date, @application_submitted_date) <= 7Filtering after converting a value
Use try_convert when a value needs to be treated as a different data type before the formula compares it.
try_convert(int, @score_value) >= 90Avoiding divide-by-zero errors
Use nullif when a formula divides by a value that could be zero.
(@email_interactions / nullif(@total_interactions, 0)) >= .5Combining conditions
Use and or or when more than one condition needs to be true. Use parentheses to make the intended logic clear.
(@person_last LIKE 'A%') or (@person_last LIKE 'B%')