How to add a Calculated Column?

Last Updated on:
June 20, 2022

Predefined Views by your Admin can not be edited. Only Views from the My Views section can be edited.

1. Click on the Columns Tab to open the columns menu. Click on the arrow next to the “Add Column” button and choose “Calculated Column”.

2. Click on “Calculated Column” and a pop-up window will open. In the pop-up window enter the name of the calculated column. The name of the Calculated Column can not start with a number nor contain special characters.

3. Choose the type of data format (Text, Number, Date Time and Yes No).

4. In the Formula Expression field enter column identifiers and operations needed. In the right-side of the pop-up, under “Available Columns”, only columns visible in the View will be listed (including previously added calculated columns).

5. Hovering over each of the available columns, a copy sign appears. Click on the copy sign to copy the column identifier and paste it into the “Formula Expression” field. 

6. Take into consideration the “Type” of data used in “Formula Expression” with respect to the expected return type of data in the “Calculated Column”. One can not use “Text” fields in Formula Expression and expect “Number” as the return data type in the Calculated Column. “Type” of data used in “Formula Expression” and obtained in “Calculated Column” do not need to match exclusively. For example, any “Type” of data used in “Formula Expression” can be converted to “Text”.

7. If the Calculated column is operating with the Number type of data, to divide two values, copy/paste the identifier of the first column, enter division sign (/) and copy/paste the identifier of the second column into the “Formula Expression” field.

8. “Save” button becomes active upon entering valid inputs in the Formula Expression field. Click on the “Save” button, the pop-up window will close and the Calculated Column will be in the View.

Operators and Functions in Calculated Columns

1. Different operators and functions are available for use with Number, Text, Yes No and Date and Time type of data (see Table 1). To calculate the average value of all rows in one column, copy/paste the identifier of the column (Number type of data) in the “Formula Expression” field and use the AVERAGE function. The result of this calculation will be shown as a value of the Calculated Column.

To calculate the average value for each row, add values of the columns and divide them with the number of columns. 

To concatenate two or more text values, choose the “Text” type of the Calculated Column. In the “Formula Expression” field copy/paste the identifier of the first column, add “+” sign (concatenating operator) and copy/paste the identifier of the second column. 

To perform logical disjunction of two operands use logical operator OR. Returns true if any or both of the operands is true, otherwise it is false. In the given example the value of the Calculated Columns is “No” because the “Inventory Management” (left operand) is “Not Checked”. 

Apply DATE_ADD function to Date and Time type of data to calculate the date three months from the “Created On” date

2. Calculated Column could be used in “Formula Expression” for further calculations just like all other columns available in the View. Copy the name of the Calculated Column and paste it into the “Formula Expression” field. 

How to Edit, Remove and Show/Hide Calculated Columns

1. Click on the Columns tab, the added Calculated column will be in the bottom of the list of available columns. Hovering over, options to Edit, Remove or Show/Hide the column appear. Click on the “6 dots” and drag and drop the column to change the order. 

2. If the values in the Calculated Column are numbers, besides Edit, Remove and Show/Hide, an option to Edit precision is also available. Click on “Edit precision” and choose the Number Format from the drop down menu. 

3. If the values in Calculated Columns are of Date and Time format, besides Edit, Remove and Show/Hide, an option to Edit Date Format is also available. Click on “Edit Date Format” and choose the Date Format from the drop down menu.