Standard formula syntax
Introduction¶
When a formula is created in a financial statement template, its arguments, i.e. the FST items and/or accounts, are defined, together with the logical operators that establish relationships between the arguments. In the formula definition area, once the desired element type has been indicated, CCH Tagetik provides suggestions to help with its creation.
Syntax of arguments in formulas¶
The definition, in formulas, of elements based on FST items or accounts is guided by CCH Tagetik.
Note: when you type [ in the formula definition area, depending on the origin the system will show the elements of the chosen dimension of this financial statement template, other financial statement templates, or accounts depending on what is selected in the "add element from" field.
| If you want to add... | Then... | Example |
|---|---|---|
| an FST item of the same template | typing [ in the formula definition area will open a drop-down menu showing a list of FST items in the template. The syntax for the element is *[ |
|
| an FST item from other templates | typing [ in the formula definition area will open a drop-down menu showing a list of all the other existing financial statement templates. The syntax for those items is *[ |
|
| an account | typing [ in the formula definition area will open a drop-down menu showing a list of existing accounts in the table. The syntax for the element is [ |
With current financial statement template 010 - Sales revenue, selecting account 10010 - Cash in Bank Accounts will create the formula element [10010]. |
Operators and functions in formulas¶
A list of supported operators is provided below.
| Element | Description |
|---|---|
| Algebraic operators | - + - - - * - / |
| Logical operators | - = - <> - > - < - >= - <= - && - |
| Functions | - IF (logical_test, value_if_true, value_if_false) - OR (logical1, logical2) - AND (logical1, logical2) - NOT (logical) - 1 (= TRUE) - 0 (= FALSE) |
Note: when items are saved, the system highlights any syntax errors.
Scenario and period elements¶
The string syntax onScenarioPeriod() can be used to calculate KPIs with reference to scenarios and periods other than those present in the form to which the financial statement template belongs. This can be applied to FST items and accounts, and accepts the following arguments:
- a positive or negative number indicating the time difference in terms of period (e.g. -2 = two previous periods, 2 = two subsequent periods)
- two strings between double inverted commas (") for the scenario code and period code
- reference to a scenario/period using cell fields (see Cell fields).
To separate the account code or account/item and the onScenarioPeriod element, "." is used as a separator.
| Reference scenarios and periods | Element description | Example of syntax |
|---|---|---|
| Previous period of the current scenario | - financial statement template PL - item 0010 - 2 previous periods of the current scenario | *[PL |
| current scenario/period | - financial statement template PL - item 0010 - current scenario/period | *[PL |
| a specific scenario (e.g. 2014BDG), period (e.g. 12): | - financial statement template PL - item 0010 - scenario 2014BDG - period 12 | *[PL |
| last period of the current quarter | - account 0010 - last period of the current quarter | [00010].onScenarioPeriod(quarter().lastSP()) |
| last period of the third to last quarter 3 | - account 0010 - last period of the third to last quarter 3 | [00010].onScenarioPeriod(quarter().offsetSP(-3).lastSP()) |
Example
In a formula with reference to account 00010 read from the current period for period 01, and read from the previous period for all other periods, the syntax is as follows:
IF($Period.code == "01", [00010], [00010].onScenarioPeriod(-1))