Computed fields

Concept Information

DEFINE FIELD . . . COMPUTED command

In an Analytics table, a computed field is a field that displays the results of an expression, rather than actual physical data. Computed fields typically perform some kind of calculation, operation, or data conversion.

For more information about expressions, see Using expressions.

How are computed fields useful?

The physical data you work with provides the basis for analysis, but frequently you need to extrapolate information from the physical data, or perform calculations, to move your analysis forward.

Without altering the physical source data, computed fields allow you to extrapolate and calculate. They are "virtual fields" that you can use to create useful data that does not directly exist in the physical data sources you are working with.

Some uses for computed fields

Display the result of a calculation In an inventory file, you create a computed field called Value that multiples the Quantity field by the Unit_cost field to calculate the total value of each inventory item.
Convert a physical data field to a required data type In order to work with a numeric field as if it were character data, you create a computed field that uses the STRING( ) function to convert the numeric values to character values.
Using conditions, substitute text values for numeric codes You create a conditional computed field that displays the actual names of countries by mapping them to the numeric country codes in a physical field. For example: “Canada” instead of 01, and “USA” instead of 02.
Evaluate one or more conditions and determine the value of the field based on the result You create a conditional computed field that calculates the tax on an item based on the region where it is sold. If the item is sold in one region the tax is calculated at 7%. If it is sold in another region the tax is calculated at 6%.

Data category of computed fields

Just like physical fields, computed fields belong to one of the following data categories:

  • character
  • numeric
  • datetime
  • logical

Unlike physical fields, you do not explicitly select a data type, and by extension a data category, when defining a computed field. Instead, the Default Value you specify for a computed field dictates the computed field data category.

The table below provides examples of computed field default values and the associated data category.

Computed field default value Computed field data category
"Location Unknown" Character
STRING(Employee_number, 10)

0.00

Numeric

Quantity * Unit_cost

VALUE(Salary, 0)
`20180331` Datetime
CTOD(Invoice_date, "DD/MM/YYYY")
T Logical
Value > 1000

Controlling decimal precision in numeric computed fields

In a numeric computed field, the decimal precision of all numeric computed values is controlled by the precision of the expression or the literal value specified in the Default Value field.

  • expression if you specify the default expression Invoice_Amount * 0.375, and values in the Invoice Amount field have two decimal places, all computed values are calculated to three decimal places, and rounded if necessary.

    The decimal precision of expressions is governed by the rules outlined in Controlling rounding and decimal precision in numeric expressions.

  • literal value if you specify the default value 0.00, all computed values are calculated to two decimal places, and rounded if necessary.

Increase the decimal precision

To increase the decimal precision of numeric computed values, increase the number of decimal places in the Default Value field.

Expression

Multiply an expression by 1 followed by the number of decimal places of precision that you want. Make sure to position the 1 at the start of the expression. The example below increases the precision to four decimal places:

1.0000 * Invoice_Amount * 0.375

Literal value

Add trailing zeros to the decimal portion of a literal value. The example below increases the precision to three decimal places:

0.000

Types of computed fields

You can create two types of computed fields, which are described in subsequent sections:

  • Basic computed field
  • Conditional computed field:
    • with literal values
    • with computed values

Basic computed field

A basic computed field uses a single expression or literal value and applies it to all the records in a table, regardless of the value in each record.

Example of a basic computed field

You want to verify the total inventory value at cost for each product in an inventory report.

You create a computed field, Inventory Value check, that multiplies the Quantity on Hand field by the Unit Cost field. You can compare the values calculated by the computed field to the reported values to see if they match.

The example below shows the definition for the Inventory_Value_check computed field in the Table Layout dialog box. The computed expression ( QtyOH * UnCst ) appears in the Default Value field.

In the table view, you can position the computed field (Inventory Value check) beside the physical, source data field (Inventory Value at Cost), and compare values.

You can also create a filter that returns any non-matching values:

Inventory_Value_check <> Inventory_Value_at_Cost

Define a basic computed field

Define a computed field that uses a single expression or literal value and applies it to all the records in a table, regardless of the value in each record.

Specify the name and the default value of the computed field

  1. Select Edit > Table Layout.
  2. In the Edit Fields/Expressions tab, click Add a New Expression .
  3. Enter the name for the field in the Name text box.

    Note

    Field names are limited to 256 upper and lowercase alphanumeric characters. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

    Analytics has a number of reserved keywords that cannot be used as field names. For a complete list, see Reserved keywords.

  4. Do one of the following:
    • Enter an expression or a literal value in the Default Value text box.

      This method is only suitable for simple expressions.

    • Click f(x) to create an expression using the Expression Builder.

      For more information, see Creating expressions using the Expression Builder.

    Note

    For numeric computed fields, the decimal precision of all numeric computed values is controlled by the precision of the expression or the literal value specified in Default Value.

    For more information, see Controlling decimal precision in numeric computed fields.

Specify field metadata

  1. (Optional) Specify the display width for the field in characters in the Width text box.

    The Width value is used as the column size when displaying the field in Analytics views and reports.

  2. (Optional) Specify the display name in the Alternate Column Title text box.

    The display name is used as the column heading, instead of the field name, when displaying the field in Analytics views and reports. If a value is not specified, the field name is used.

  3. If required, specify values for one or more of the settings listed below.

    The data category of the expression or the literal value that you specified in the Default Value text box dictates which settings are enabled.

    SettingData categoryDescription
    FormatNumeric only

    Controls the display format of numeric fields in views and reports.

    You can select the format from the drop-down list, type in the format manually, or edit a format from the list after you have selected it.

    If the Format drop-down list is blank, the default display format specified in the Options dialog box applies to the data in the field. The format you specify here overrides the default format.

    Suppress TotalsNumeric only

    Prevents the values in the field from being totaled.

    Analytics automatically totals numeric fields in reports. Some numeric fields contain information that should not be totaled, such as unit prices or account numbers.

    Static 

    Alters the default behavior Analytics uses when evaluating an IF statement associated with the field. (For more information about the optional IF statement, see Finalize the field definition.)

    Static deselected (default) if the IF statement evaluates to False, the field is assigned an empty value – blank, zero (0), or False (F), depending on the data category of the field.

    Static selected if the IF statement evaluates to False, Analytics repeats the last valid value in the field rather than using an empty value. The last valid value is repeated in each row until the IF statement evaluates to True and a new valid value is used.

    Datetime 

    This option is not available for computed fields.

    Control TotalNumeric only

    Specifies that the field is a control total field.

    A control total is the sum of values in a numeric field, which can be used to check data integrity. When you extract or sort data to a new table, Analytics includes the input and output totals of a control total field in the table history. Input refers to the original table. Output refers to the new table. If the two totals match, no data was lost in the extract or sort operation.

    You can also compare the control totals computed by Analytics with those supplied by a data provider to determine whether you received all the data.

    If you specify control totals for more than one field, the table history reports on only the numeric field with the leftmost starting position.

    Default FilterLogical only

    Filters the records in the default view based on the value of the field (True or False). Only records that evaluate to True are displayed.

    The filter is applied automatically each time the Analytics table containing the field is opened.

Finalize the field definition

  1. (Optional) If you want to limit the records evaluated by the computed field, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
    • evaluated by the computed field records that satisfy the IF statement
    • not evaluated by the computed field records that do not satisfy the IF statement

    For example, the IF statement Invoice_Amount >= 1000 prevents records with invoice amounts less than $1000 from being evaluated.

    For excluded records, the computed field values are blank, zero (0), or False (F), depending on the data category of the computed field. You can undo the exclusion at any point by deleting the IF statement.

  2. (Optional) Deselect Add created field to current view if you do not want the new computed field to be automatically added to the open table view.

    If you leave the option selected, the new field is added to the table view. The field is positioned as the last column in view, or to the left of any selected column in the view.

    You can manually add a field to a view at any time. For more information, see Add columns to a view.

  3. (Optional) If you want to add a note to accompany the field definition, click Edit Field Note , enter the note text, and click Close .
  4. Click Accept Entry .

    Analytics adds the computed field to the table layout. You can use the field in commands or reports.

  5. Click Close to exit the Table Layout dialog box.

    The associated column is added to the table view if you left Add created field to current view selected.

Conditional computed field

A conditional computed field contains multiple expressions or literal values and applies them to the records in a table on a conditional basis. The particular expression or literal value applied to each record depends on the value in the record.

Example of a conditional computed field with literal values

You want to assign a literal value of "Small", "Medium", or "Large" to each record depending on the size of the invoice amount.

You create a computed field, Invoice size, that identifies the size of the invoice amount in each record, and assigns the correct literal value:

  • "Small" amounts less than $5,000.00
  • "Medium" amounts from $5,000.00 to $9,999.99
  • "Large" amounts $10,000.00 and greater

The example below shows the definition for the Invoice size computed field in the Table Layout dialog box. The literal value "Small" appears in the Default Value field. The literal values "Medium" and "Large" each appear in a separate condition.

Each condition contains a computed expression that must evaluate to True in order for the associated value to be used in the computed field. If a record satisfies neither of the conditions, the default value is used.

Note

The order in which conditions are listed is important. For more information, see List conditions from most restrictive to least restrictive.

 

In the table view, the invoice size now appears with each record.

You can also create a filter that displays only the records of one size:

Invoice_size = "Large"

Example of a conditional computed field with computed values

You want to calculate the discount amount for each record based on a discount percentage that varies with invoice size.

You create a computed field, Discount amount, that identifies the size of the invoice amount in each record, and computes the discount amount using the correct percentage:

  • 0% discount amounts less than $5,000.00
  • 10% discount amounts from $5,000.00 to $9,999.99
  • 15% discount amounts $10,000.00 and greater

The example below shows the definition for the Discount amount computed field in the Table Layout dialog box. The literal value 0.00 appears in the Default Value field. The computed values Invoice_Amount * 0.10 and Invoice_Amount * 0.15 each appear in a separate condition.

Each condition contains a computed expression that must evaluate to True in order for the associated computed value to be used. If a record satisfies neither of the conditions, the default value is used.

Note

The order in which conditions are listed is important. For more information, see List conditions from most restrictive to least restrictive.

 

In the table view, the discount amount now appears with each record.

You can also create a filter that displays discounts greater than a certain amount:

Discount_amount >= 750

List conditions from most restrictive to least restrictive

When you define multiple conditions, Analytics evaluates them in the order that they are displayed in the condition list in the Table Layout dialog box, starting at the top.

In the Invoice size example above, invoice amounts are tested against the conditions in this order:

Order Condition Value
1 Invoice_Amount >= 10000 "Large"
2 Invoice_Amount >= 5000 "Medium"

To ensure that records that meet more than one condition are processed in the way that you intend, list conditions from most restrictive to least restrictive, with the most restrictive condition at the top.

What does "restrictive" mean?

"Restrictive" refers to the proportion of a set of values that is eligible to meet a condition. The more restrictive a condition, the smaller the eligible proportion.

Consider the following set of values:

  • $12,000
  • $8,000
  • $7,000

Least restrictive condition All three values meet the condition Invoice_Amount >= 5000. The condition is the least restrictive because the entire set of values is eligible.

Most restrictive condition Only $12,000 meets the condition Invoice_Amount >= 10000. The condition is the most restrictive because only one of the values in the set is eligible.

How Analytics assigns conditional computed field values

For each record, Analytics assigns the computed field value associated with the first condition that evaluates to True. Once assigned, a computed field value is not changed, even if a record satisfies a subsequent condition.

Consider an invoice amount of $12,000:

  • Assigned value = Large With the order of conditions above, the amount is assigned the value of Large, which is correct because the amount is greater than $10,000.
  • Assigned value = Medium If you reverse the order of the conditions, the amount is assigned the value of Medium, which is also correct because the amount is greater than $5,000. However, the assignment of values is not working the way you intended because Invoice_Amount >= 5000 is a less restrictive condition and it is capturing amounts that you do not want it to capture.

Think of restriction in terms of subsets

Another way to think of restriction is in terms of subsets. Values eligible to meet the first listed condition should form the smallest subset of a set of values. With each additional condition, the size of the eligible subset grows, and contains all previous subsets.

Keep in mind that once Analytics assigns a computed field value to a record, the value is not changed. So in the example below, "Large" is assigned to the record containing an invoice amount of $12,000, and even though the record satisfies Condition 2, the value is not updated to "Medium".

Define a conditional computed field

Define a computed field that contains multiple expressions or literal values and applies them to the records in a table on a conditional basis.

Specify the name and the default value of the computed field

  1. Select Edit > Table Layout.
  2. In the Edit Fields/Expressions tab, click Add a New Expression .
  3. Enter the name for the field in the Name text box.

    Note

    Field names are limited to 256 upper and lowercase alphanumeric characters. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

    Analytics has a number of reserved keywords that cannot be used as field names. For a complete list, see Reserved keywords.

  4. Do one of the following:
    • Enter an expression or a literal value in the Default Value text box.

      This method is only suitable for simple expressions.

    • Click f(x) to create an expression using the Expression Builder.

      For more information, see Creating expressions using the Expression Builder.

    Note

    For numeric computed fields, the decimal precision of all numeric computed values is controlled by the precision of the expression or the literal value specified in Default Value.

    For more information, see Controlling decimal precision in numeric computed fields.

    Literal text values must be enclosed in quotation marks (" "). Literal date values must be enclosed in backquotes (` `).

Specify field metadata

  1. (Optional) Specify the display width for the field in characters in the Width text box.

    The Width value is used as the column size when displaying the field in Analytics views and reports.

  2. (Optional) Specify the display name in the Alternate Column Title text box.

    The display name is used as the column heading, instead of the field name, when displaying the field in Analytics views and reports. If a value is not specified, the field name is used.

  3. If required, specify values for one or more of the settings listed below.

    The data category of the expression or the literal value that you specified in the Default Value text box dictates which settings are enabled.

    SettingData categoryDescription
    FormatNumeric only

    Controls the display format of numeric fields in views and reports.

    You can select the format from the drop-down list, type in the format manually, or edit a format from the list after you have selected it.

    If the Format drop-down list is blank, the default display format specified in the Options dialog box applies to the data in the field. The format you specify here overrides the default format.

    Suppress TotalsNumeric only

    Prevents the values in the field from being totaled.

    Analytics automatically totals numeric fields in reports. Some numeric fields contain information that should not be totaled, such as unit prices or account numbers.

    Static 

    Alters the default behavior Analytics uses when evaluating an IF statement associated with the field. (For more information about the optional IF statement, see Finalize the field definition.)

    Static deselected (default) if the IF statement evaluates to False, the field is assigned an empty value – blank, zero (0), or False (F), depending on the data category of the field.

    Static selected if the IF statement evaluates to False, Analytics repeats the last valid value in the field rather than using an empty value. The last valid value is repeated in each row until the IF statement evaluates to True and a new valid value is used.

    Datetime 

    This option is not available for computed fields.

    Control TotalNumeric only

    Specifies that the field is a control total field.

    A control total is the sum of values in a numeric field, which can be used to check data integrity. When you extract or sort data to a new table, Analytics includes the input and output totals of a control total field in the table history. Input refers to the original table. Output refers to the new table. If the two totals match, no data was lost in the extract or sort operation.

    You can also compare the control totals computed by Analytics with those supplied by a data provider to determine whether you received all the data.

    If you specify control totals for more than one field, the table history reports on only the numeric field with the leftmost starting position.

    Default FilterLogical only

    Filters the records in the default view based on the value of the field (True or False). Only records that evaluate to True are displayed.

    The filter is applied automatically each time the Analytics table containing the field is opened.

Specify conditional values of the computed field

Conditional values are set up as condition-value pairs. If a record meets the Condition, the computed field uses the specified Value.

Note

The values you specify, and the Default Value, must all be the same data type.

  1. Click Insert a Condition .
  2. In the Add a Condition and Value dialog box, do the following, then click OK:
    1. Enter an expression in the Condition text box, or click Condition to create an expression using the Expression Builder.
    2. Enter a value in the Value text box, or click Value to create an expression using the Expression Builder.

      Here are conditional values from the two examples above:

  3. If you want to create another condition, do one of the following:
    • Click Insert a Condition  and repeat the steps above.
    • If you want to create a condition that is similar to an existing condition, select the condition you want to copy, click Duplicate Condition , and then click Edit Condition and Value  to modify the settings for the new condition.
  4. (Optional) Select a condition and click Move Condition Up  or Move Condition Down  to change the order in which it is evaluated.

    Note

    The order in which conditions are listed is important. For more information, see List conditions from most restrictive to least restrictive.

Finalize the field definition

  1. (Optional) If you want to limit the records evaluated by the computed field, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
    • evaluated by the computed field records that satisfy the IF statement
    • not evaluated by the computed field records that do not satisfy the IF statement

    For example, the IF statement Invoice_Amount >= 1000 prevents records with invoice amounts less than $1000 from being evaluated.

    For excluded records, the computed field values are blank, zero (0), or False (F), depending on the data category of the computed field. You can undo the exclusion at any point by deleting the IF statement.

  2. (Optional) Deselect Add created field to current view if you do not want the new computed field to be automatically added to the open table view.

    If you leave the option selected, the new field is added to the table view. The field is positioned as the last column in view, or to the left of any selected column in the view.

    You can manually add a field to a view at any time. For more information, see Add columns to a view.

  3. (Optional) If you want to add a note to accompany the field definition, click Edit Field Note , enter the note text, and click Close .
  4. Click Accept Entry .

    Analytics adds the computed field to the table layout. You can use the field in commands or reports.

  5. Click Close to exit the Table Layout dialog box.

    The associated column is added to the table view if you left Add created field to current view selected.