Creating Calculated Columns

A calculated column is a custom column added to a query that calculates results or links text fields.

To add a calculated column, begin by choosing Edit→Calculated Columns. Choose + to create a calculated column and open the Calculated Column Designer. Choose the data type and use the condition editor to define the parameters of the calculated column. Three different data types can be used in Calculated Columns:

• Text - alphanumeric information (sometimes called strings)
• Number - numeric information that supports calculations
• Date - store dates and/or time information

The Calculated Column Designer has 3 distinct sections:

• Column Settings (Top Left): Provide a name, data type of the output, and formatting (if applicable)
• Tables and Fields (Bottom Left): Allows you to select the table and fields. Note that each data type is in a separate pane.
• Formula Builder (Right): Area where you build your formula and add operators

1. Click on the appropriate box in the work area where you want to place the field.
2. Locate the field to be added by selecting the appropriate data type and expanding the appropriate table.
3. Drag the field into the selected box

   Tip: Locate the fields you want to use in your formula in the Query Designer first, as they are listed in alphabetical order in that interface.

1. Click on the appropriate box in the work area where you want to place the operator.
2. Drag the operator to the appropriate location in the formula, that is, to the left or right of a field.

   Tip: If you click an operator, it will add it as the last item in the selected box.

In the calculated column designer, tokens allow you to add additional information that is not contained in a field.

You can add static text, number, and dates. For example, if you want to use a formula that divides an annual salary by 12, you would use a token that stores the number 12. You will use a token that stores the number 12 in the formula.

You can also use a token to represent some dynamic data. For example, you can use a token to represent today’s date. That means every time someone runs the report, it will use the current date in the formula.

To delete a part of the formula, drag it into the box labeled Trash in the lower right.

A concatenation is the joining of two data strings, in this case text fields. First, select the data type as Text and add two or more fields to the workspace by dragging them into the Value box.

For example, you can create a text concatenation that will show a user's full name:

• Set the data type to Text.
• Add the text field, First Name.
• Add the second text field, Last Name.

An example of this is as follows: "Employee" "First Name" + "Last Name" (for example, "Kenneth Roden" "Kenneth" + "Roden").

Use number formulas to calculate sums, differences, products, and quotients. First, select the data type as Number and add two or more fields to the workspace with the appropriate operators. You can edit the format to show decimal places or to change the number to a percent.

For example, you can create a number formula that will show a user's combined salary:

• Set the data type to Number.
• Add the field Total Pay.
• Add the + operator.
• Add the field Bonus.

An example of this is as follows: "Base & Merit" "Total Pay" + "Bonus" (for example, "56000" "50000" + "6000")

The date formula can either be a number or a date data type. If using a date data type, you can edit the format to post the dates using numbers or to use a custom format.

When creating a calculated column that will be used to calculate the number of days for an event, use Number as the data type, and then select a Date field.

For example, to calculate days overdue:

• Set the data type to .
• Add the Form End Date date field.
• Add the - operator.
• Add the field Form Due Date date field.

An example of this is as follows: "Form Overdue Days" "Form End Date" - "Form Due Date" (for example, "4" "01/20/2013" - "01/16/2013")

If / Then / Else statements compare two or more sets of data and test the results. If the results are true, the THEN instructions are taken; if not, the ELSE instructions are taken.

When creating a calculated column that will be used to calculate if/then/else statements, use Number, Text, or Date as the data type. Then choose the if / then / else statement icon on the workspace:

For example, to calculate gender:

1. Set the data type to Text.
2. Choose the If/Then/Else statement icon.
3. On the IF line, add the Gender field, the = operator, and M text.
4. On the THEN Value line, add Male.
5. On the ELSE Value line, add Female.
6. If necessary, use the && and OR icons to add in more statements.

Example: If Gender M, Then Male Else Female.

You can nest IF statements to provide more than 2 possible outcomes. Nesting means to put one IF statement inside another as part of the THEN or ELSE. For example, you could convert the number of days of the week into text. You would need 7 possible outcomes so you would nest 6 IF statements.

You can utilize functions in the calculated column editor. Functions perform an operation on a field or token to return a different value than the original.

The calculated column editor has several text functions and date functions.

1. Click on the field or token.
2. Click Edit by the function.
3. Select the appropriate function.
4. Supply any additional parameters required for the function.
5. Click OK.

The calculated column designer has two types of date functions:

Year, Month, Quarter, Day functions all return a numeric value for the portion of the date of the input. For example, if the input value of the hire date is February 12, 2022, then the result of the functions would be:

Year 2022

Quarter 1

Month 2

Day 12

You can also use the Age function, based on a selected date column. You must supply a parameter of the comparison date, either a static date or a dynamic date such as today.

Results will be in whole numbers and increment on the same day and month as the selected date column.

The age function can be used to calculate the age of a person (using Birthdate) or the age of a tenure (using Hire Date). Choose the date field from which to calculate age and set the Age function.

The calculated column designer has three text functions. They all return part of the input text.

You can input their text length requirements and choose whether they would like the left, middle, or right part of a selected text. Choose a field that you would like to extract a few characters from. Set the middle, left or right function and the number of characters to show.

Example: If the National lD has a "-" at the second character, show the first 5 characters in Document Number, if the National lD has a "-" at the fourth character, show the first 6 characters in Document Number.

You may re-use common formulas in Calculated Columns by sharing a calculation. Other users can quickly and easily bring the shared calculation into their queries and adjust the formula in their query as required.

You can also control whether a calculated column is displayed in the query results. You set it by choosing the green/red bubble in the Calculated Column window. It will toggle between green (on) and red (off).

For example, you may have a calculated column that returns an individual’s tenure in days, then a second calculated column that converts the days into years. In this case, you do not wish to display the calculated column that is the number of days.