Using calculated columns
Add computed fields to your report using formulas and expressions.
Calculated columns let you create new fields in your report using formulas -- no need to modify your underlying dataset. Use them for ratios, percentage calculations, conditional logic, and any derived metric your team needs.
Adding a calculated column
In the report builder, open the Columns section in the left panel.
Click Add Calculated Column at the bottom of the column list.
Give the column a Name -- this is what appears in the report header (e.g., "Response Rate %" or "Score Category").
Enter your Formula in the formula field. Formulas reference other columns by their column ID.
Choose the output Type (number or text) so Genuics formats the results correctly.
Click Save. The calculated column appears in your report like any other column.
Formula syntax
Formulas use a straightforward expression language. You can combine column references, numbers, arithmetic operators, and functions.
Column references
Reference a column by wrapping its ID in square brackets:
[responses] / [total_sent] * 100
Column IDs are shown in the formula editor's autocomplete. Start typing a column name and Genuics will suggest matching columns.
Arithmetic operators
| Operator | Meaning | Example |
|---|---|---|
+ | Addition | [revenue] + [tax] |
- | Subtraction | [total] - [returns] |
* | Multiplication | [quantity] * [unit_price] |
/ | Division | [score] / [max_score] |
Available functions
Genuics supports the following functions in calculated column formulas:
| Function | What it does | Example |
|---|---|---|
ABS(x) | Absolute value | ABS([change]) |
ROUND(x, n) | Round to n decimal places | ROUND([avg_score], 1) |
FLOOR(x) | Round down to nearest integer | FLOOR([rating]) |
CEIL(x) | Round up to nearest integer | CEIL([rating]) |
GREATEST(a, b, ...) | Largest of the given values | GREATEST([q1], [q2], [q3]) |
LEAST(a, b, ...) | Smallest of the given values | LEAST([q1], [q2], [q3]) |
COALESCE(a, b, ...) | First non-empty value | COALESCE([primary_score], [fallback_score]) |
NULLIF(a, b) | Returns null if a equals b | NULLIF([total], 0) |
IF(condition, then, else) | Conditional logic | IF([score] >= 9, 1, 0) |
Conditional logic with IF
The IF function is especially useful for categorizing data:
IF([nps_score] >= 9, "Promoter", IF([nps_score] >= 7, "Passive", "Detractor"))
You can nest IF statements to handle multiple conditions.
Example: calculating response rate
Suppose your dataset has a "responses" column and a "total_sent" column, and you want to show the response rate as a percentage.
Add a calculated column named "Response Rate %."
Enter the formula:
ROUND([responses] / NULLIF([total_sent], 0) * 100, 1)Set the type to number and optionally set the format to percent.
Run the report. The new column shows the response rate for each row, calculated on the fly.
Calculated columns in grouped reports
When your report has grouping enabled, calculated columns participate in subtotals. The formula is applied after the aggregation, so the subtotal row recalculates the formula using the aggregated values of the referenced columns.
For example, if you have a calculated column [revenue] / [orders] (average order value), the subtotal row divides the summed revenue by the summed orders -- giving you the correct weighted average, not an average of averages.
Calculated columns referencing other calculated columns
You can reference one calculated column from another. Genuics resolves the dependency chain automatically. However, circular references (Column A references Column B, which references Column A) are not allowed and will show an error.
What's next
For more on how grouping and aggregation interact with your columns, see Grouping, filtering, and aggregation.