Calculating Ratios in Query Formulas

You can calculate ratios in query formulas by dividing two aggregate values or by averaging a binary Existence output. Both approaches can return the same result. The better choice depends on the shape of the data, the ratio being calculated, and the complexity of the query.

For example, an event attendance rate can be calculated as:

Number of attendees / Number of registrations = 6 / 10 = 0.60

   

       

⭐ Get Inspired

   

   

This article was adapted from a post by Technolutions staff in the Slate Community Forums' Get Inspired space. Have a great idea for a Get Inspired post? Let us know!

Comparing the two approaches

Building a ratio by dividing two aggregate exports

Use this approach when you want to calculate a ratio from two explicit counts, sums, or other aggregate values.

1. In the query, add a Subquery Export.

2. Set Output to Formula.

3. Add a subquery export or aggregate export that returns the numerator. For an attendance rate, this is the number of attended registrations.

4. Add a subquery export or aggregate export that returns the denominator. For an attendance rate, this is the total number of registrations.

5. Rename each export so it can be referenced cleanly in the formula, such as attended and registered.

6. In the Formula section, divide the numerator by the denominator.

7. Select Save.

Building a ratio by averaging a binary Existence export

Use this approach when each row in the denominator group can be assigned a 1 or 0 based on whether it belongs to the numerator group. For an attendance rate, each registration can be evaluated as attended or not attended.

In an event with 10 registrations where six registrations are attended and four are not attended, the binary values look like this:

1 + 1 + 1 + 1 + 1 + 1 + 0 + 0 + 0 + 0 = 6

The average of those values returns the same ratio:

(1 + 1 + 1 + 1 + 1 + 1 + 0 + 0 + 0 + 0) / 10 = 0.60

1. In the query, add a Subquery Export for the row set that represents the denominator. For an attendance rate, this is the registration row set.

2. Add an inner Subquery Export.

3. Set Output to Existence.

4. Configure the Existence export so rows that meet the numerator condition return 1 and rows that do not meet the condition return 0.

5. Return to the top-level subquery export.

6. Set the top-level output to average the binary Existence export.

7. Select Save.

📝 Note

The binary-average approach is appropriate for subpopulation ratios. It works when the denominator is a set of rows and each row can be classified as either included in the numerator or not included in the numerator.

Checking the results

When both approaches are configured for the same attendance rate, they should return the same value.

Preview the query and compare the two outputs before using the ratio in reports, dashboards, exports, or automation. If the values differ, review the filters, row set, and aggregate settings for each subquery export.

Further reading

📖 Subquery Exports & Outputs

📖 Using Formulas in Queries

📖 Query Building Best Practices