Computed fields

In an Analytics table, a field that displays the results of an Analytics expression, rather than actual physical data, is called a computed field. 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.

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.

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.

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.

Analytics 14.1 Help