Calculated Fields

From DMXDeepInsight
Jump to: navigation, search

Overview

Calculated fields let you automate manual calculations using a formula based on other fields and existing calculated fields. You can create calculated fields in the calculated field editor using functions and operators. The newly created calculated field appears in the Data pane with a fx icon to indicate that it is a calculation. If the calculation is a text string or a date, it appears in the Dimensions Pane, or if it is a numeric value, it appears in the Measures Pane. If your calculation is invalid, then an error message is displayed at the bottom of the calculated field dialog so that you can troubleshoot.

Data Types

DeepInsight supports string, number, date, and date and time datatypes and lists them in Fields. The box below Fields contains the field types that are dimensions, measures, parameters, calculated fields of your worksheet in alphabetical order. You can filter field types based on the data type you choose in Fields. For example, clicking Date, DeepInsight shows Order Date and Ship Date. When you click any field type, DeepInsight shows you more information about that field in the far-right corner of the dialog. For example, click Ship Date to see its data type as Date and field type as Dimension. When you double-click a field type, DeepInsight inserts it into the Expression box. Field types are case-sensitive and require brackets [] around them when used in a formula.

Functions

DeepInsight supports Aggregate, Date, Logical, Math, and String functions and lists them in Functions. You can filter function types based on the function you choose. When you click a function, DeepInsight shows you more information about that function in the far-right corner of the dialog, e.g., function expression, description, and example. When you double-click a function, DeepInsight inserts it into the Expression box.

Create calculated fields

You can click anywhere in the Data Pane to create a calculated field. If you right-click a dimension or measure and create a calculated field, then DeepInsight creates an expression in the calculated field editor for the selected dimension or measure field. Follow the instructions below to create a calculated field from the Parameters Pane.

  1. In the Data Pane, right-click in the Parameters Pane, and then click Create Calculated Field.
  2. In the Create Calculated Field dialog, perform the following actions.
    • Rename the calculated field.
    • In the Expression box, create a calculation using any field or function. You can also copy-paste your calculation.
    • When finished, click Save. The new calculated field appears with a 'ƒx' icon indicating it is a calculation. It appears in the Dimensions Pane if it returns a text string or a date, or in the Measures Pane if it returns a numeric value.

Manage calculated fields

You can edit, duplicate, rename, and delete existing calculated fields.

  • Edit - If you have used a calculation in the chart view, then your edit actions can affect your chart.
  • Rename - When you rename a calculated field from the Data Pane, the changes are immediately reflected everywhere.
  • Delete – You cannot delete calculated field used in other calculations. To delete such calculated fields, remove them from other calculations.

Edit calculated fields

  1. In the Data Pane, right-click a calculated field, and then click Edit.
  2. In the Edit Calculated Field dialog, make modifications as necessary.
  3. When finished, click Save.

Duplicate calculated fields

To duplicate a calculated field, in the Data Pane, right-click a calculated field, and then click Duplicate. The duplicate calculated field is listed right below the original calculated field with “<calculated field name>(Copy <numeric value>)” appended to its name.

Rename calculated fields

Use this option only when you want to rename a calculated field and do not require to make any other changes. To make multiple changes to a calculated field, you can refer to Edit calculated fields.

  1. In the Data Pane, right-click a calculated field, and then click Rename.
  2. In the Rename Field dialog, provide a new name for the calculated field.
  3. When finished, click Rename.

Delete calculated fields

To delete a calculated field from the Data Pane, right-click the calculated field and then click Delete. Calculated fields used in another calculation cannot be deleted.