Define an advanced formula

The Advanced Formulas page allows you to create and save custom functions that facilitate arithmetic calculations such as financial ratios, ROI (Return on Investment), and KPIs (Key Performance Indicators). These formulas are stored within the application for future use, ensuring that you can easily access and use the formula whenever required, without the need for recreating it each time. By saving the formulas within the application, you can maintain consistency and efficiency in your calculations.

To access the Advanced Formulas page, click Configuration > System Structures > Advanced Formulas.

This page is divided into 3 sections:

1. Groups

2. Functions

3. Tests

Groups section

The Groups section gives you a way to logically put various functions into one entity and link that entity with a specific triggering behavior so that those functions are run after a specified action.

To create a new group:

1. Click .

2. Enter a Name for the group.

3. Select a Type of behavior for the group. Options include:

   • After data entry - the group starts after a user clicks on a data entry page.

   • After data import - the group starts after an import file is processed.

   • Before consolidation - the group starts before consolidation begins during a consolidation.

   • Manually - the group starts only when manually selected.

4. Enter a Description for the group.

5. Click .

Functions section

The Functions section allows you to run your formulas for one or more companies and see what the application has

retrieved from the source and sent to the target.

To create a new function:

1. Click .

2. In the Add to group field, update the group if necessary, by making a selection from the drop-down. You can add the same function to multiple groups if needed.

3. Enter a Name for the function.

4. In the Order in this group field, specify the sort order.

5. To enable the function, select Active in this group. (The Active option allows you to disable some functions without having to remove them).

6. Fill in the Source instruction. See list of available functions.

7. Fill in the Destination instruction. See list of available functions.

8. Click .

Tests section

This section allows you to run your formula and see what the application retrieves for the source and would send to the target.

Select the formula you want to test and click .

Available functions

The available functions are:

AdjustAccount

• Goal

  Retrieve an amount at consolidated level according to a specified journal/journalview for a given company (if empty group), a given account (or summation account / if empty all accounts), eventually for a given flow (if empty/closing amount) and or partner company and or dimension codes.

• Parameters when used as Source:

  Required
  Period (=>Implicitly set to @ContextPeriodCode)

  Optional

  ReferencePeriod

  Account

  Company

  PartnerCompany

  Flow

  Dimension

  Journal

  BeforeAfter
  

    Example:  

  AdjustAccount( Account:[Specific:"PLINC"]

  Company:"4″

  BeforeAfter:"A"

  Journal:"M001")

• Parameters when used as Destination:
  Not supported
  

 

BundleAccount

• Goal

  Retrieve (or save) an amount at bundle level for a given company (if empty group), a given account (or summation account / if empty all accounts) Note: Interco or flow are only available after local adjustments cf. BundleAdjustAccount

• Parameters, when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

  Optional

  ReferencePeriod

  Account

    Example:  

  BundleAccount(Period:@ContextPeriodCode

  Company:@contextcompanycode

  Account:[Sum:"P16″] )

• Parameters, when used as Destination:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Account

  Company

    Example:  

  BundleAccount( Period:@ContextPeriodCode

  Company:@contextcompanycode

  Account:[Specific:"PLINC"] )

 

BundleAdjustAccount

• Goal

  Retrieve (or save) an amount at adjusted bundle level for a given company (if empty group), a given account (or summation account / if empty all accounts), eventually for a given flow (if empty closing amount), and/or partner company and/or dimension codes.

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

  Optional

  ReferencePeriod

  Account

  Journal

  PartnerCompany

  Flow

  Dimension

    Example:  

  BundleAdjustAccount( Period:"200812ACT001"

  Company: "1"

  Flow: "100"

  Account:"001")

    Example:  

  BundleAdjustAccount( Period:@ContextPeriodCode

  Company: @ContextCompanyCode

  Account:"700000″

  Dimension:[group:"ER01"

  dim1:"Geothermal energy"] )

• Parameters when used as Destination:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Account

  Company

  Optional

  PartnerCompany

  Flow

  Dimension

    Example:  

  BundleAdjustAccount( Period:"200812ACT001"

  Company: "1"

  Flow: "100"

  Account:"001")

 

CurrencyClosingRate

• Goal

  Retrieve the closing rate of a specified currency

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  CurrCode

    Example:  CurrencyClosingRate( Curr:"USD" )

• Parameters when used as Destination:

  Not supported

 

CurrencyAverageRate

• Goal

  Retrieve the average rate of a specified currency

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  CurrCode

    Example:  CurrencyAverageRate ( Curr:"USD" )

• Parameters when used as Destination:

  Not supported

 

CurrencyAverageMonth

• Goal

  Retrieve the default monthly rate of a specified currency

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  CurrCode

    Example:  CurrencyAverageMonth ( Curr:"USD" )

• Parameters when used as Destination:

  Not supported

 

CompanyGroupCtrlPerc

• Goal

  Retrieve the group control percentage of a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyGroupCtrlPerc ( Cpy:"4")

• Parameters when used as Destination:

  Not supported

 

CompanyGroupPerc

• Goal

  Retrieve the group percentage of a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyGroupPerc ( Cpy:"4")

• Parameters when used as Destination:

  Not supported

 

CompanyMinorPerc

• Goal

  Retrieve the non-controlling percentage of a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyMinorPerc ( Cpy:"4")

• Parameters when used as Destination:

  Not supported

 

CompanyNbrFinRightsIssued

• Goal

  Retrieve the number of shares issued by a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyNbrFinRightsIssued( Cpy:"4")

• Parameters when used as Destination:

  Not supported

 

CompanyNbrVotingRightsIssued

• Goal

  Retrieve the number of voting shares issued by a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyNbrVotingRightsIssued ( Cpy:"4")

• Parameters when used as Destination:

  Not supported

 

CompanyTaxRate

• Goal

  Retrieve the default tax rate of a given company

• Parameters when used as Source:

  Required

  Period =>Implicitly set to @ContextPeriodCode

  Company

    Example:  CompanyTaxRate ( Cpy:"4")

• Parameters when used as Destination:

  Not supported

Pre-existing contextual variables

@ContextPeriodCode

Retrieve the full period code of the current working period to be used as parameter in a function:

Period:@ContextPeriodCode

Current working period

@ContextYear

Retrieve the year of the current working period to be used as parameter in a function:

Period:[ Year: @ContextYear Month:"12"Category:"FCT" Sequence: "0"]

Year of the working period, but month is always December and category is always FCT and sequence is always 0

@ContextMonth

Retrieve the month of the current working period to be used as parameter in a function:

Period:[ Year: @ContextYear Month: @ContextMonth Category:"FCT" Sequence: "0"]

Month and year of the working period, but category is always FCT and sequence is always 0

@ContextCategory

Retrieve the category of the current working period to be used as parameter in a function:

Period:[ Year: @ContextYear Month: "12" Category: @ContextCategory Sequence: "0"]

Category and year of the working period, but month is always december and sequence is always 0

@ContextSequence

Retrieve the sequence of the current working period to be used as parameter in a function:

Period:[ Year: @ContextYear Month: "12" Category: @ContextCategory Sequence: @ContextSequence]

Sequence, Category and year of the working period, but month is always december

@ContextReferenceCode

Retrieve the full period code of the current reference period to be used as parameter in a function:

Period:@ContextReferenceCode

@ContextCompanyCode

Retrieve the company code of the company to compute the function for to be used as parameter in a function:

Cpy:@ ContextCompanyCode

Note :

One can use ‘+’ or ‘-‘ to increase or decrease one context variable by a value:

Period:[ Year: @ContextYear Month: @ContextMonth-1 Category: @ContextCategory Sequence:@ContextSequence]

Previous month of current period

Advanced use of parameters

As seen here above, the available parameters (depending of the function) are the following:

• Period (or prd)
• ReferencePeriod (or ref)
• Account (or acc)
• Company (or cpy)
• PartnerCompany (or partner)
• Flow
• Dimension
• Journal (or jnl)
• BeforeAfter (or bfaf)
  • Possible values:
    • After A
    • Before B
    • CurrCode (or Curr)

Each of them accepts a fixed value between double quotes like:

·Cpy:"4″

·Curr:"USD"

·…

They also accept context variables (seen above) when applicable, like:

·Cpy:@ContextCompanyCode

·…

For some of them, you may enter advanced detail between square brackets to specify the value to use as follows:

• Period:[ Year: @ContextYear Month: @ContextMonth-1 Category: @ContextCategory Sequence:@ContextSequence]
  • Previous month of current period
• Cpy:[POV: "POV1" CRIT: "YourCriteria"]
  • Select all companies from the selected POV
• Acc:[Specific: "PLINC"]
  • Return the code of a specific account
• Acc:[SUM: "PL01"]
  • Return the total value of a summation account
• Dimension:[group:"ER01"dim1:"Geothermal energy"]
  • Selection of a value for a specific dimension

Available operators

Mathematical operators may be used in the source instruction to create complex operation.

The available operators are:

Operator	Description	Example	Result
+	addition	2 + 3	5
-	subtraction	2 - 3	-1
*	multiplication	2 * 3	6
/	division	4 / 2	2

Logical operators may also be used in the source instruction to create complex operation.

Operator	Description
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to
=	equal
<>	not equal

A conditional (IF) statement may also be used in the source instruction to create complex operation.

IF ( AdjustAccount( Account:"136800"Company:"4") > 0 )

THEN

AdjustAccount( Account:"136800"Company:"4") * 10

ELSE

AdjustAccount( Account:"136800"Company:"4") * -10

ENDIF

Custom variables

Custom variables may also be defined in the source instruction in order to define multiple loops with different values to use for each of those loop.

  Note:  Each row of the variable section must end with a semicolon.

Multiple destinations

The computed result may also be sent to multiple destinations at once.

In order to do that, you have to separate the multiple destination formulae by a semicolon in the Destination Instruction area.

You may also add a sign symbol (‘+’ or ‘-’) in front of the formulae to send the value with the same or an opposite sign.