The TGKFIELD function
Description¶
The TGKFIELD function allows you to obtain the value of a field cell.
Syntax¶
The function uses the following syntax: =TGKFIELD(dataSource, "CellFieldExpression", "DimensionName1: value1", "DimensionName2: value2", ...)
Arguments¶
The items that can be added to the function are detailed below.
Datasource¶
Code of the database from which the value must be recovered. This code can be indicated in the following ways:
- with the database code, e.g. =TGKRET("TCPM_DEMODEV_001", ...)
- with the TGKCURRDB function, e.g. =TGKRET(TGKCURRDB(), ...)
- with reference to the Excel cell containing the database code, e.g. = TGKRET(A25, ...)
Cell field¶
The cell field to be defined, e.g. $Entity.code.
For more details on cell fields, see Cell fields.
Selecting dimensions¶
To retrieve the value of the desired dimension, you can do as follows.
- To retrieve a value of a dimension:
: , e.g. for entity A00, specify Entity:A00. - To retrieve a value of a dimension on which aggregation structures exist, refer to a node:
: , e.g. Entity:H_CODE:*H_NODE.:* - To retrieve a value of an account dimension:
:* , e.g. Account:*FST_CODE|FST_NODE
The supported dimensions are listed below.
| Dimension | Description |
|---|---|
| Account: | Account code. Mandatory dimension. Individual accounts, nodes of an account hierarchy or an FST item can be used. In order for the formula to be correct, the account must be present in the list and must meet one of the following conditions: - be related to an FST. - be present in a control group. - be related to a node of an accounts hierarchy. |
| AmountType | Amount type. Mandatory dimension. The possible values are: - TIP_O: (original) amount type of data entered in the system by the user. Default value when the dimension is not defined. - TIP_OV: (in transaction currency) amount type that only represents the amounts expressed in a currency other than the default entity currency (for example, an amount in euros to be attributed to an entity whose currency is the dollar) - TIP_OE: proportional amount type - TIP_OEV: proportional amount type in transaction currency - TIP_PR: converted amount type - TIP_PRV: amount type converted into transaction currency - TIP_CV: consolidable amount type - TIP_CVV: consolidable amount type in transaction currency - TIP_DES: (description) amount type that allows the user to enter and save text-based information, such as notes or comments. |
| Category | Category code |
| Currency: | Currency code . If the dimension is not defined, it uses the entity currency. |
| Cust_Dim1..Cust_Dim5: | Element code of one of the custom dimensions. For example, if the products to recover the value of a certain product are handled on custom dimension 1, the code is Cust_Dim1. |
| Entity | Entity code |
| ICCust_Dim: | Element code of the intercompany custom dimension |
| ICEntity | Intercompany entity code |
| Period | Period code. Mandatory dimension. |
| PeriodLength: | Period length. The possible values are: - LUN_0: progressive - LUN_1: monthly - LUN_2: bimonthly - LUN_3: quarterly - LUN_4: every four months - LUN_5: every five months - LUN_6: half-yearly - LUN_12: annual - LUN_QTD: Quarter to date - LUN_STD: Semester to date - LUN_YTD: Year to date |
| Scenario | Scenario code. Mandatory dimension. |
| DB code | If the formula used in the current application database is not defined (similar to using the TGKCURRDB function) |
Note: the system does not filter any data on undefined dimensions.