AX2022

Calculation types for calculated fields

Calculation types define the calculations to be used for calculated fields. Axiom ships with a standard set of calculation types. The following calculation types are included by default:

  • TotalToPeriod1 - TotalToPeriod12
  • Q1Total - Q4Total
  • YearToDate
  • CurrentQuarterToDate
  • CurrentPeriod

The default calculation types can be customized, and new calculation types can be added. This is an advanced feature that is typically only done by Axiom consultants. If your system has installed products, those products may include customized and/or additional calculation types.

Aggregation types

The aggregation type of a calculation defines how the values for the calculation type are calculated. The following aggregation types are supported:

Aggregation Type Code Description
Summation 1 Sums all columns in sequence through the current or alternate period.
Average 2 Returns the average of all columns in sequence through the current or alternate period.
Last in Set 3 Returns the last column in the sequence through the current or alternate period.
Weighted Average 4 Returns the weighted average of all columns in sequence through the current or alternate period. Weights are defined within the sequence.
Subtraction 5 Returns the difference of the current or alternate period minus the prior period. No subtraction is performed for the first period.

For example, the standard YearToDate calculation type uses the aggregation type of summation (1). This means that periods in the sequence up through the current period are summed to result in the YTD value. If the current period is 3, the value is calculated by adding P1+P2+P3.

Each calculation type is assigned a default aggregation type. Calculation types can also use alternate aggregation types. This allows certain records in the table to be calculated using a different aggregation type based on a filter criteria statement. For example, for financial institutions, calculation types may require different aggregation types depending on whether the account is a balance sheet account.

The standard "out-of-the-box" calculation types are tuned to general industry. All totals are basic summation (1), and the current period is the value of that period (3). No conditional (alternate) calculations apply.

Creating or editing calculation types

You can edit the calculation types to:

  • Add calculation types that are not included in the standard set.
  • Edit the existing calculation types for industry-specific needs.

Currently, the only way to manage calculation types within Axiom is by using Save Type 4 to edit the Axiom.Calculations table. For more information, see Managing calculation types using Save Type 4.

Each calculation type has the following properties:

Field Description

CalculationName

The name of the calculation type, as it will appear in the selection list when defining a calculated field.

TimeType

The associated time type for the calculation:

  • FullSequence
  • Quarter

This determines whether the calculation takes into account all of the columns in the sequence, or just the columns within a quarter.

IsTiedToCurrent

Specifies whether the calculation is tied to the current period:

  • If True, then the calculation is dependent on the current period, as defined for the system or for a specific table. For example, YearToDate calculates through the specified current period.
  • If False, then the calculation ignores the current period.

NOTE: If both IsTiedToCurrent and IsTiedToAlternate are false, then the calculation uses all available columns (dependent on TimeType).

IsTiedToAlternate

Specifies whether the calculation is tied to the alternate period:

  • If True, then the calculation is dependent on the alternate period specified for the calculation type (in the AlternateCurrent field). This is a fixed value that does not change. For example, TotalToPeriod8 calculates through the specified alternate period of 8.
  • If False, then the calculation ignores the alternate period.

AlternateCurrent

The alternate period to use for the calculation, if IsTiedToAlternate is True. For example, TotalToPeriod8 is set to 8.

DefaultAggregationType

The default aggregation type to be used for the calculation.

  • Summation
  • Average
  • Last in Set
  • Weighted Average
  • Subtraction

If all records can use the same aggregation type for this calculation, then no further settings are required. However, if some records need to use a different aggregation type, then you can set SupportsAlternateAggregation to True, and then use the aggregation condition columns to specify which records should use certain alternate aggregations.

SupportsAlternateAggregation

Specifies whether the calculation supports alternate aggregation types for certain records, based on a criteria statement.

If True, the calculation works as follows:

  • If a record matches a criteria statement entered into one of the aggregation condition columns, that aggregation type is used for the record instead of the specified default aggregation type.
  • Otherwise, the default aggregation type is used.

If False, the default aggregation type is used for all records.

Aggregation condition columns:

  • ConditionForSummation
  • ConditionForAverage
  • ConditionForWeightedAverage
  • ConditionForLastInSet
  • ConditionForSubtraction

If SupportsAlternateAggregation is True, you can enter a criteria statement into one or more aggregation condition columns. Records that match the criteria statement will use the aggregation type of that column instead of the default aggregation type.

These columns do not apply if SupportsAlternateAggregation is False.

If multiple alternate aggregation types are used, and a conflict exists (a record matches the filter criteria statement in multiple columns), then the aggregation type used for the record is the “first-applied” aggregation type (in the order listed to the left).

For example, if a record matches the criteria statement in the AggregationConditionforSummation column and also matches the criteria statement in the AggregationConditionforAverage column, then the Summation calculation is used for that record.

Example

To edit the standard YearToDate calculation for use by financial institutions, you would make the following changes:

  • Set the DefaultAggregationType to Weighted Average.
  • Set SupportsAlternateAggregation to True.
  • Specify a criteria statement for the ConditionForSummation, to define the set of accounts that should calculate using summation instead of weighted average. For example: acct.bsis='is' and dtype.dtype='iseom'
  • Specify a criteria statement for the ConditionForLastInSet, to define the set of accounts that should calculate as the current period value instead of weighted average. For example: acct.bsis='bs' and dtype.dtype='bseom'

Calculated fields assigned to this edited YearToDate calculation type will be calculated as follows:

  • Accounts that match the condition in AggregateConditionForSummation will be summed up through the current period.
  • Accounts that match the condition in AggregationConditionForLastInSet will use the current period value.
  • All other accounts will use the weighted average, using the weights defined for each period in the associated sequence.