Purpose
This guide outlines how to add calculated columns to Views, provides an introduction to operators and functions, and demonstrates how to edit or remove these columns.
Instructions
1. Click the Columns tab to open the columns menu. Click on the arrow next to the Add Column button and select Calculated Column.
2. A pop-up window will open. In the pop-up window enter the Name of the calculated column.
Note: The name of the Calculated Column can not start with a number or contain special characters.
3. Choose the type of data format (Text, Number, Date Time, and Yes No).
4. In the Formula Expression field enter the 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 icon appears. Click the icon to copy the column identifier and paste it into the Formula Expression field.
Note: Take into consideration the Type of data used in the Formula Expression, with respect to the expected return type of data in the Calculated Column. For example, you cannot use Text fields in the Formula Expression and expect a Number as the return data type in the Calculated Column. However, the Type of data used in the Formula Expression and obtained in the Calculated Column do not need to match exclusively. Any Type of data used in the Formula Expression can be converted to Text.
6. If the Calculated column is operating with the Number type of data, to divide two values: copy and paste the identifier of the first column, enter the division sign (/) and then copy and paste the identifier of the second column into the Formula Expression field.
7. The Save button becomes active upon entering valid inputs in the Formula Expression field. Click the button to close the pop-up window and the Calculated Column will be in the View.
Using Operators and Functions
Different operators and functions are available for use depending on your data type selected (see Table 1).
Examples
1. To calculate the average value of all rows in one column, copy and paste the identifier of the column (Number data type) 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.
2. To calculate the average value for each row, add values of the columns and divide them with the number of columns.
3. To concatenate two or more text values, choose the Text data type for the Calculated Column. In the Formula Expression field, copy and paste the identifier of the first column, add the + sign (concatenating operator) and then copy and paste the identifier of the second column.
4. To perform a logical disjunction of two operands, use the logical operator OR. This 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.
5. Apply the DATE_ADD function to a Date and Time data type calculated column to calculate the date three months from the Created On date.
6. A Calculated Column could be used in the 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.
Edit, Remove and Show/Hide Calculated Columns
1. Click on the Columns tab to display the list of available columns. The added Calculated column will be at the bottom of the list. Hovering over the column will display options to Edit, Remove or Show/Hide the column appear.
2. Click the 6 dots icon to drag and drop the column to change the order in which it is displayed in the View.
3. If the values in the Calculated Column are numbers an additional option to Edit Precision is also available. Click the Edit Precision icon and choose the Number Format from the drop down menu.
4. If the values in Calculated Columns are of Date and Time format, an additional option to edit the Date Format is also available. Click the Edit Date Format icon and choose the Date Format from the drop down menu.
