Skip to content

Formulas for financial statement template (FST) items

Standard formulas

A standard formula is a combination of FST items or accounts defined with the following syntax:

[ ...]

Element Description
Input element - current FST items - items from other FSTs, if of the same type - accounts, in the financial statement templates based on the Account dimension only
Operators arithmetical: +, -, *, / logical: IF, OR, AND, NOT,

The following syntax must be used to refer to an FST item:

[V,,'']

The following syntax must be used to refer to an item in another FST of the same type:

[V,'','']

IMPORTANT: before saving the formula, the system will detect and flag up any errors regarding use of the operators, allowing the user to correct them.

Example

To define a Formula item by adding together the item 50010 (from the current FST) and 90010 from another FST (code 020), the following formula must be used:

[V,'','50010'] + V,'020','50010']

Other syntax examples

It is often useful to calculate KPIs by referring to different scenarios and periods from those included in the form in which the FST is used. For this reason, the input elements may be enriched with the specific time shifts required, using the following syntax.

Requirement Syntax
FST item V01 relating to previous periods of the current scenario [V,,'V01'].onScenarioPeriod(-1)
FST item V01 relating to the current scenario/period [V,,'V01'].onScenarioPeriod($Scenario.code, $Period.code)
FST item V01 relating to a specific scenario (e.g. 2014BDG), period (e.g. 12) [V,,'V01'].onScenarioPeriod("2014BDG","12")
Account ‘'00010' relating to the last period of the current quarter [C,'00010'].onScenarioPeriod(quarter().lastSP())
Account ‘'00010' relating to the last period of the third last quarter 3 [C,'00010'].onScenarioPeriod(quarter().offsetSP(-3).lastSP())

The onScenarioPeriod() utility can be applied to FST items and accounts, and accepts the following:

  • a positive or negative number that indicates a difference
  • two strings that specify the scenario code and the period code
  • a scenario / period directly, through the use of cell fields

The reference to the scenario or period may be used to guide the logical conditions.

Example of an IF condition

Account 00010 relating to the current period if we are in period 01, otherwise Account 00010 relating to the previous period:

IF($Pe- riod.code == "01", [C, '00010'], [C, '00010'].onScenarioPeriod(-1))

TGKML MD formulas

A TGKML MD formula is a multi-dimensional formula written with syntax belonging to Tagetik TGKML, deriving from the MultiDimensional eXpressions (MDX) language for OLAP (online analytical processing) on the SQL Server.

These formulas may be defined using a dedicated editor and by respecting the following syntax:

  • TGKML 1.0 header at the beginning of the script, which is mandatory in an MD calculation logic, except for MD FST formulas.
  • The element resulting from the calculation and displayed on the cell making the reference is identified by the reserved element @output. This is a standard temporary account, the result of which is displayed in the cell that makes reference to the MD formula.

For example: @output :=010, 50010}+{010, 50020};

  • Only parent FST items may be referred to and not for the custom dimension (advanced financial statement templates) of the current financial statement template or of other templates.
  • It is not possible to use temporary accounts as entire parameters of the ‘time skip’ or ‘number of months’ functions, for example Lag(), Span(), etc.
  • An MD financial statement template can also be used on reporting forms, which are therefore not linked to a specific process; it is therefore not possible to omit the company hierarchy when reference is made to a company node (Entity.[|NN] syntax), i.e. the organisational hierarchy specified in the definition of the process.
  • Conversion functions cannot be used (ConvertOriginal).
  • In an MD FST formula, it is not possible to define assignments to physical accounts to perform DB writing. It is instead possible to use assignments to temporary accounts, which can then be reused to calculate the final result (identified by the @output specification – reserved and case-sensitive). If, at the end of the script, several records exist in the @output temporary account, the amounts will be added up.
  • Since the final result is always a dimensionless number, it is pointless to specify the output Pro directive (periodic result year-to-date calculation).

Use of TGKML MD formulas in the forms

These formulas can be used in the forms, respecting the following conditions:

  • Forms that use the in-memory engine.
  • The MD script is run cell by cell, filtering every run with the filters working on the cell.
  • The currency is always the one identified by the cell in the form to which the calculation is being applied. The amount in the company currency coincides with the amount in the original currency.
  • Data are always read from the “cube” identified by the definition of the form (matrix) in which the financial statement template is being used. The result is always a number shown on the relative cell of the report, and not an n-tuple saved in the DB.