Column types

Calculation column

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.
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
 for the column type.
There are several options which can be configured to control how 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
Select decimal places
You can set the number of decimal places displayed, if any, up to a maximum of 5.
Formula
Enter your formula in the available box. Click to select column names and operators from a drop-down list.
After you create your formula, use
Validate formula
 to check the formula provides a valid result.
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
.
Additional settings
You can select options for the column such as separators, or if weekends are included when calculating days:
  • 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.
  • 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.
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. Use
[Today]
,
-
, and
Created date
The formula must be validated before saving or updating the column. To do so, select
Validate formula
. Enter sample numbers for each column in the
Check formula
 window.
Select
Evaluate
 to see the value calculated by the formula. If this matches your expectations select
Agree
 to validate the formula. If it does not, close the window and check your formula.

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)
.

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.
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
.

This article applies to:

  • Product: HighQ
  • Subject: iSheets, Setup

Related content