AX2635
About calculated fields
Each table can have a set of calculated fields, such as total, year-to-date, and quarter-to-date. These fields are calculated automatically by Axiom Software.
To add calculated fields to a table, you must do the following:
- Define a sequence of columns to be used as the basis of the calculation.
- Specify the calculation to be performed.
For example, a table may have twelve columns of budget data representing one year of planning (NYB1 to NYB12). These twelve columns can be defined as a sequence named M (for months). You can then define a calculated field named TOT for sequence M, that uses calculation type TotalToPeriod12. Axiom Software will maintain a database field named TOT which, when queried, returns a total value for the specified sequence. This field can be referenced in Axiom queries and Axiom functions to return a total value, instead of needing to bring in the twelve columns and manually calculating the value.
NOTE: If using aliases, then the database columns should be named something like P1-P12, with alias names of NYB1-NYB12 pointing to those columns. You can also define alias names for the calculated fields. For example, the calculated field can be named TOT, with an alias of NYB_TOT.
Calculated fields are calculated on demand, whenever a query is made to the field. Calculated field values are not pre-calculated and stored. There is no need to "recalculate" the fields after data or system changes.
Referencing calculated fields
For the most part, calculated fields behave just like other columns in a table. You can query calculated field data by using the calculated field name (for example, GL2018.TOT or just TOT if the table is assumed). You can also define alias names for calculated fields, and then reference them using the alias names.
In areas where they can be used, calculated fields display in lists of table columns, such as in the Axiom Sheet Assistant. However, they do not display in areas where you can only work with literal table columns, such as when using Open Table in Spreadsheet, or when copying table data.
Calculation types
Each calculated field is assigned a calculation type that specifies the calculation to be performed. Axiom Software provides a number of default calculation types that can be modified as needed for industry-specific or organization-specific requirements. Additional calculation types can also be added.
Modifying and creating calculation types is an advanced function that should only be performed by implementation consultants, Axiom Software support, or advanced users.
