Calculation columns display the result of mathematical calculations performed using values contained in one or more date and time, number or calculation columns. A calculation column may use Today's date, columns, numbers and mathematical operators to generate a value.
The number format is flexible and can display symbols for currency or percentages, and a choice of dots or commas as separators.
Please ensure you update to API v18 to use number and currency formats.
Number and calculation columns included via a lookup column may not be used for calculation columns.
Calculation columns require no user input; they are automatically populated with the result of the equation configured by an admin and performed against the value of number columns in a single iSheet record.
Select Add column; enter a name for the column, then select Calculation.
There are several options which can be configured in order to control the way in which values are entered and displayed.
Number format
As of October 2023, you can define the number format displayed in the column. You can choose to either display:
- a comma as the thousands separator, with a dot as the decimal separator
- a dot as the thousands separator, with a comma as the decimal separator
The number and symbol format is applied when an iSheet is exported to CSV, XML or PDF documents (but not XLSX).
The thousand separator is only displayed if Show thousand separators is selected, below.
Select decimal places
You can set the number of decimal places displayed, if any, up to a maximum of 5, as seen below.
Formula
Enter your formula in this box. Click to select column names and operators from a drop-down list.
After you create your formula, use Validate formula to check the
Column width
- Column width - The width of the column displayed when the iSheet is viewed in the iSheets section. entries that are too long continue on a new line. The minimum column width is 20 and the maximum is 650.
Select symbol
As of October 2023, the Select symbol menu includes currency and percentage symbols.
Select the drop-down menu to select a symbol:
Then select if the symbol appears Before the number or After the number.
If you select the percentage sign (%), no additional calculation is made, the symbol is simply added to the value in the column.
The number and symbol format is applied when an iSheet is exported to CSV, XML or PDF documents (but not XLSX or Export templates).
Additional settings
You can select options for the column such as separators, or if weekends are included when calculating days:
Even if this is enabled, thousand separators do not appear when users are editing iSheet columns; they appear when a user views the column in iSheet item and table views.
- Allow search - Deselect this setting to hide the content of this column from search results.
- Add to the default view - When the column is created add the column to the default View.
This setting is only available when you first add the column to the iSheet.
- Do not count weekends (only count Mon- Fri) - You can exclude weekends (Saturday and Sunday) from a formula that counts days between two dates. This allows durations to be based on a five-day working week, providing more accurate estimates for calculations of elapsed time.
Column conditions
Conditional columns allow you to create forms that show fields based on your use case or project. Columns may be displayed or hidden based on other columns in the record. See Configure column conditions in iSheets for more information.
Create a formula with columns
The Add column page for calculation columns allows you to create formulas with the name of the columns and simple operators such as +. -, / and *. You may use parentheses '(' and ')' to isolate calculations in a complex formula.
Select the formula field:
Use the column names and operators in the drop-down list to build your formula.
You can also type the name of the column or type an operator to filter the list.
Tap Enter to confirm a typed number or operator. We recommend you select column names from the list.
An example formula is given in the Add column page next to the formula field: (Revenue-Expense)/(Revenue*Tax)+Wages+10
Today's date
Select [Today] to insert today's date into a formula. This value represents a rolling value for the current date (not just the day the column was created).
For example, you can calculate how long it has been since a record was created:
The formula must be validated before saving or updating the column. To do so, click Validate formula. Enter sample numbers for each column in the Check formula window:
If you see an Invalid formula message, please check your formula.
You may only use date values and operators in a date calculation. Numerical calculations, such as '- 2' or '/ 7' are not accepted. You may use the tools provided in workflow to further adjust dates.
Click Evaluate to see the value calculated by the formula. If this matches your expectations click Agree to validate the formula. If it does not, close the window and check your formula.
Please note, 'today's date' is only available in calculation columns, not data visualisation or workflow.
Calculation columns in the iSheets module
As calculation columns require no user input, the calculation column is not displayed when users add or edit iSheet items. When a record is saved, the calculation is displayed in the table view:
Calculation column considerations
Calculation columns have the same configuration settings as number columns, and admins should configure appropriate settings for the results of the calculation column:
- Select decimal places
- Show thousand separators
- Show as percentage
Calculations may only be performed against values entered into a single iSheet item or row, and do not aggregate values of multiple columns. For example, the calculation column cannot be used to calculate the total sum of the Sales column for all items in an iSheet.
Calculating workdays between two dates
You can exclude weekends (Saturday and Sunday) from a formula that counts days between two dates. This allows durations to be based on a five-day working week, providing more accurate estimates for calculations of elapsed time.
To only count Monday to Friday, open the Calculation column settings and select Do not count weekends (only count Mon- Fri).
The calculation counts a partial day as a full day, i.e. it rounds up to the next full day.
Changes due to daylight savings are included.
Public holidays are ignored and do not affect the calculation - they are counted as normal days.
Best practice
Blank records and calculations
If the number columns used in the calculation column are left blank, the calculation cannot be made and displays NaN ('not a number') instead of a value.
A blank value in a number column is NOT equivalent to zero.
For this reason, it is best practice to make any number columns used in calculation columns mandatory. If appropriate for the use case, admins may apply a default value to number columns.
Calculating the sum of records
A calculation column is designed to perform calculations on a set of related columns. To calculate the sum of a series of records in a number column, please use a data visualisation panel.
