Calculations and totals
Two separate features are covered here:
Calculation columns compute a value per row from other columns in that row.
Footers compute a value per column across all rows.
They can be combined - a Calculation column can itself have a footer that sums the calculated values.
Calculation columns
Add a column of type Calculation, and the Calculation Formula Configuration section appears.
Referencing other columns
Write the column's name in double quotes:
("Quantity" * "Unit Price") + "Tax"
Only three column types can be referenced in a formula:
Type | Value used |
|---|---|
Number | The number |
Decimal | The number |
Checkbox |
|
Text, Date, Select, User, File and other Calculation columns cannot be used. The form lists the columns you can reference under Available Variables.
Operators
+ - * / and parentheses ( ).
Standard precedence applies: * and / bind more tightly than + and -.
Unary minus works before a number or a column name - -"Discount" is fine - but not before a bracket. -("A" + "B") is rejected. Write 0 - ("A" + "B") instead.
There are no functions
No SUM, no IF, no ROUND, no MIN/MAX. A formula is arithmetic over the row's own values and nothing more.
If you need a total down a column rather than across a row, that is what a footer is for - see below.
Decimal places
Decimal Places controls how the result is displayed. The default is 2.
Building and validating a formula
Generate fills the field with a valid random example built from the panel's own numeric columns. It is the quickest way to see the correct syntax for your particular columns.
Validate checks the formula. The column cannot be saved until the formula validates, and editing a validated formula invalidates it again - you will see "Formula has changed, please re-validate."
Formula errors
Message | Cause |
|---|---|
| The field is blank |
| The number of |
|
|
| Those columns exist but are not Number, Decimal or Checkbox |
| A quoted column name is missing its closing quote |
| A character that is not a number, letter, quote, operator or bracket |
| A |
| Only occurs at runtime, when a row's divisor is 0 |
What users see when a calculation fails
Calculation cells are read-only and recalculate live as their inputs change. Three display states:
State | Shown | Tooltip |
|---|---|---|
Normal | The value, rounded to the configured decimal places | The formula |
No inputs yet |
| No Value |
Error | Calculation Error in red | The reason, e.g. |
A divide-by-zero in one row does not break the others. Each row is calculated independently.
Footers
A footer shows a single summary value beneath a column. Configure it under Column Footer Configuration → Footer type when creating or editing the column.
Column type | Available footers |
|---|---|
Number, Decimal, Calculation | No Footer, Average, Min, Max, Sum |
Checkbox | No Footer, Percentage Checked, Percentage Unchecked |
Text, Date, Select, User, File | None - footers are not available |
How each one displays
Footer | Example | Meaning |
|---|---|---|
Sum |
| All numeric values added together |
Average |
| Sum divided by the number of numeric values |
Min |
| The smallest numeric value |
Max |
| The largest numeric value |
Percentage Checked |
| Percentage of rows where the checkbox has a value |
Percentage Unchecked |
| Percentage of rows where it does not |
Values are rounded to the column's decimal places, or 2 where none is set.
Things to know about footers
The footer row appears in view mode only. It is hidden while you edit, and recalculates when you save.
Columns without a footer configured show
-.Cells that are blank or contain text are skipped, not counted as zero. An Average over a column with three filled rows and two blanks divides by three.
A SmartList with no rows has nothing to total, so every footer cell shows
-.
Worked example: a quote line
Column | Type | Settings |
|---|---|---|
Item | Text | - |
Quantity | Number | Footer: Sum |
Unit Price | Decimal | - |
Discounted? | Checkbox | Options: |
Line Total | Calculation | Formula below, Decimal Places 2, Footer: Sum |
Formula for Line Total:
("Quantity" * "Unit Price") - ("Quantity" * "Unit Price" * "Discounted?" * 0.1)
Because a ticked checkbox evaluates to 1 and an unticked one to 0, the second term subtracts 10% only on discounted lines. The footer on Line Total gives the quote total, and the footer on Quantity gives the total item count.
This is the standard trick for conditional arithmetic: since there is no IF, multiply by a checkbox to switch a term on and off.