The TGKRETF function

Description

From TaGetik RETrieve Fixed, the TGKRETF function allows you to retrieve, according to arguments set in a fixed order, a value from a database other than the one on which the form is run. In relation to the TGKRET function, the position of the filter parameters is fixed.

Syntax

The function uses the following syntax: =TGKRETF("Scenario", "Period", "Account", ["AmountType"], ["Entity"], ["Category"], ["Cust_dim1"], ["Cust_dim2"], ["Cust_dim3"], ["Cust_dim4"], ["Cust_dim5"], ["ICEntity"], ["ICCustDim"], ["Currency"], ["PeriodLength"], ["Causal"], ["Convert"], ["DBCode"])

The value retrieved by the function is then shown in the cell with which the function is associated.

Arguments

The items that can be added to the function are detailed below.

Supported dimensions

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. 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.

Data conversion

The Convert element allows the user to convert dimension data while running a form. The Convert values are:

• Final: final FX rate
• Average: average FX rate
• Period: period average FX rate
• Account: Conversion type associated with the account

The syntax is the following: <("dimensionNamex:valuex")> i.e. dimension name: Convert value.

This is directly set without specifying its name (like the other parameters).

Calculation by segment

In the TGKRET formula, it is possible to request the amount calculation in the following ways: by segment, intersegment, parent segment and parent intersegment.

To do this, the desired suffix (suffixes are case insensitive) must be concatenated to the desired dimension code (node).

Calculation method	Suffix
segment	- \s - \segment Example: \s
intersegment	- \i - \is - \intersegment Example: \i
parent segment	- \p - \ps - \parentSegment Example: \p
parent intersegment	- \pis - \pi - \parentIntersegment Example \pis

Syntax conventions and specific features

• The position of the parameters within the TGKRETF formula is fixed. If you want to indicate an optional parameter and that parameter comes after other optional parameters that you do not want to specify, they must each be identified with an empty space separated by ",".
• To retrieve several elements or nodes of aggregation structures for the same dimension, separate the values with ",". The system reads the data related to all specified elements. For example, to retrieve accounts 50010 and 40010, the syntax in the position relative to the account is 50010,40010.
• To retrieve several nodes belonging to different aggregation structures for the same dimension, the system reads the intersection data related to the elements related to the nodes. For example, to retrieve the entities related to node N1 of hierarchy H1 and node N1 of hierarchy H2, the syntax is H1:*N1,H2:*N1.

Example

To retrieve the value of the revenue for the Antivirus product for the geographical area Italy and for scenario 2012BDG, apply the following filters:

• Scenario = 2012BDG
• Period = 12
• Account = all revenue accounts, i.e. FST item 500300 - Total Revenues in FST 010.
• Product (managed on custom dimension 2) = Antivirus, with code A01
• Entity = all entities related to the Italy node (X01) of hierarchy 03 which groups entities by geographical area
• Cost Center (managed on custom dimension 1) = generic element G01
• Employee (managed on custom dimension 2) = generic element G01

The function’s syntax is =TGKRETF("2012BDG",12,"*010|50030","TIP_O","03:*X01","$AMOUNT","G01","A01","G01", , , , , ,"LUN_0", , ,TGKCURRDB())

To read the original data for account 50010 on December 2011, the function is =TGKRETF("2011ACT","12","50010")

To read the original data for account 50010 on December 2011 on the $AMOUNT category, the function is =TGKRETF("2011ACT","12","50010", , ,"$AMOUNT")

To read the original data for account 50010 on December 2011 converted into EUR at the average FX rate, the function is =TGKRETF("2011ACT", "12", "50010", , , , , , , , , , ,"EUR", , ,"Average",TGKCURRDB())