AX2644

Managing calculated fields

Each data or reference table can have one or more calculated fields. Calculated fields are used to automatically calculate values for a sequence of data columns in a table, such as TOT (total) or YTD (year-to-date).

You can create, edit, and delete calculated fields when creating or editing a table. Calculated fields can be used with data tables and reference tables (except picklist tables or KPI tables).

Adding a calculated field

Before creating a calculated field, you must have defined at least one column sequence for the table. The sequence is used to define the set of columns for the calculation.

The ability to add a calculated field to a table is limited to administrators and to users with the following security permissions: either the global Administer Tables permission, or the Allow changing table structure permission for individual tables.

  1. From the Calculated Fields tab of the Edit Table dialog, click Add Calculated Field.

    If you are creating a new table, you can manage calculated fields on the Manage Calculated Fields screen of the Create Table wizard.

    The New Calculated Field dialog opens.

  2. In the Calculated Field Name box, type a name for the field.

    This name is used in Axiom queries and Axiom functions to query data, so it should be descriptive and short. For example: YTD.

  3. From the Calculation Type list, select the calculation to be used for the calculated field, such as YearToDate.

  4. From the Column Sequence list, select the sequence to be used for the calculation.

  5. Click OK to close the New Calculated Field dialog.

  6. To save the new field, click Apply (or OK if you are finished editing the table).

Editing a calculated field

Calculated fields for tables can be edited at any time. Keep in mind the following:

  • If you change the name, this may cause errors in existing Axiom queries and functions that reference the old name. However, if an alias is set up for the calculated field, the alias is updated automatically for the new calculated field name, and any files that reference the alias will still work as expected.
  • If you change the calculation type or the associated column sequence, the values of the calculated field may change.

The ability to edit calculated fields is limited to administrators and to users with the following security permissions: either the global Administer Tables permission, or the Allow changing table structure permission for individual tables.

To edit a calculated field:

  1. On the Calculated Fields tab of the Edit Table dialog, select the calculated field that you want to edit.

    If you are creating a new table, you can manage calculated fields on the Manage Calculated Fields screen of the Create Table wizard.

  2. Click Edit Calculated Field.

  3. In the Edit Calculated Field dialog, edit the field properties as desired, and then click OK.

  4. In the Edit Table dialog, click Apply or OK to save your changes.

Deleting a calculated field

Before deleting a calculated field, you should be sure that it is not in use. Deleting a calculated field that is in use will cause errors in existing Axiom queries and functions that reference the field.

If an alias is set up for the calculated field, that alias is also deleted when the calculated field is deleted. If you want to continue using the alias but instead point it to a different column, you should edit the alias before deleting the calculated field.

The ability to delete calculated fields is limited to administrators and to users with the following security permissions: either the global Administer Tables permission, or the Allow changing table structure permission for individual tables.

To delete a calculated field:

  1. On the Calculated Fields tab of the Edit Table dialog, select the calculated field that you want to delete.

    If you are creating a new table, you can manage calculated fields on the Manage Calculated Fields screen of the Create Table wizard.

  2. Click Delete Calculated Field.

    The calculated field is removed from the list.

  3. Click Apply or OK to save your changes.
See also