¶ 1. Abstract
The T6 Planning manual provides a comprehensive overview of using formulas and functions in the software, highlighting their versatility from simple calculations to advanced operations. It explores the automatic execution of formulas in response to data changes, including the ability for manual execution through an assistant. The performance chapter offers practical tips for optimizing formula usage, identifying bottlenecks, and addressing issues in cycles. Additionally, the manual covers available functions and parameter types, aiming to provide users with a detailed understanding of formula properties and features for efficient use within the T6 Planning context.
¶ 2. Formulas and Functions
¶ 2.1 Overview
T6 Planning formulas are a powerful tool for performing calculations on model data.
T6 Planning allows the creation of simple formulas with basic operations to complex formulas with conditionals or advanced calculations. It also provides users with a wide range of built-in functions to further assist in the development of complex calculations. This manual describes in detail the properties and characteristics of the formulas available in the T6 Planning solution, aiming to provide a deeper understanding of the use of advanced configurations.
¶ 3. Formulas
¶ 3.1. Overview
In this topic, we will explain a little more about the structure and creation of T6 formulas, their characteristics, and functionalities.
Formulas are essential tools for performing calculations on data, and through them, T6 enables advanced manipulations of data stored in Fact tables.
¶ 3.2. Prerequisites
To create, view, and execute formulas in T6, the following functionalities must be enabled:
User functionalities
- Manager:
- Manage application structure/model;
- Manage formulas;
- Enable formula editing;
- Analyst:
- View formula;
Global group functionality
- Manage Explorer;
¶ 3.2.1. Formula Structure
The basic structure of a formula consists of four configuration steps, displayed in tabs within the formula panel: Formula, Scope, Description, and Data.
¶ 3.2.2. Formula
This is the first step in the formula creation process. It is mandatory to fill in. Within the formula, we will have Application, Type, Formula Group, Position, Formula Name, and Expression;
-
Application: Field that indicates in which application the formula will be created. After creation, this information can no longer be edited;
-
Type: Mandatory field where the type of formula is selected:
- Special: Formulas that calculate across the entire scope in which they are configured or when changes impact members present in them;
- In-Memory: Formulas that are not persisted in the database. They are calculated only in the scope visible to the user on the form or report. They are useful for specific calculations;
-
Formula Group: Optional field where we will select the formula group (a feature for grouping formulas within T6, used to organize formulas by context, category, or purpose);
-
Position: Non-editable field; Indicates the positioning of the formula relative to others; The positioning refers to the order in which formulas will be executed. It is automatically generated by T6 during cube publication;
T6 takes into account newly created formulas or possible changes to existing formulas. It is possible to manually position formulas in the Formula List, but when publishing the Cube, T6 will reposition them according to the best execution order.
Example:
| Position | Formula |
|---|---|
| 1 | [A] = [B] + [C] |
| 2 | [B] = [D] + [E] |
The formula in position 1 has a member [B] that is calculated by another formula. However, based on the order, the formula that calculates [B] is in the second position. When publishing the Cube, T6 will automatically reposition the formula [B] = [D] + [E] before the formula [A] = [B] + [C] because only after calculating [B] will it be possible to calculate the value of [A].
-
Formula Name: Mandatory field that must be filled in with the formula's name.
- Tip: Always try to use a name that reflects the purpose of the formula, for example, “Calculation of labor taxes”;
-
Expression: Field where the formula's details are specified. It represents the expression the formula will execute. It resembles a mathematical expression, containing an operand equated to a sequence of interspersed operands and operators, always starting and ending with an operand.
Example:
[C1] = [C2] + [C3]
[C1]: Operand that will receive the result of the expression. This operand is also known as the resulting operand;
[C2]: Operand that will be used in the calculation of the expression;
+ : Operator to be used in the calculation of the expression;
[C3]: Operand that will be used in the calculation of the expression.
In this example, the value of [C1] will be equal to the value of [C2] plus the value of [C3].
In an expression, operands can be: Member, Number, Function, and Block;
The resulting operand must obligatorily be a member, which may or may not have scope.
Among the operators are: Addition (+), Subtraction (-), Multiplication (*), Division (/), and Power (^);
In the expression field, we can use autocomplete by typing at least 3 characters (after 1 second, suggestions will be displayed), or by clicking CTRL + SPACE, which will display suggestions for insertion.
We have a button that displays the Functions available for selection:
.
Functions are helpers for creating formulas, enabling more elaborate calculations and providing access to various model information.
For additional information on functions in T6, visit: Formulas and Functions
¶ 3.2.3. Scope
Allows filtering the execution context of a formula. This way, the formula execution will be carried out according to the filters applied.
Using the scope, it is possible to filter which dimension members the formula will be executed on. If no filter is applied to the dimension, the formula will be executed on all members.
Execution filters in the scope tab are displayed as checkboxes, and the filters include:
- Execute Automatically: Allows the formula to execute automatically when saving data in a form.
Formulas with this property active are called Online Formulas; Formulas with this property disabled are called Offline Formulas.
Whenever a formula's premise is directly or indirectly altered, the formula will execute, changing its values and ensuring the formula's expression remains true.
-
Execute Only in Planned: This option is available only in Planning applications and allows the formula to execute only in the planned scenario. When this property is active, the formula's calculation will be performed only for periods configured as planned.
-
Execute Only in Actual: This option is available only in Planning applications and allows the formula to execute only in the actual scenario. When this property is active, the formula's calculation will be performed only for periods configured as actual.
If you want the formula to run in both scenarios, Planned and Actual, uncheck both boxes.
-
Allow Persist Zero: By selecting the checkbox, the formula can save the value zero in the fact; Normally, in T6, the value zero is not saved in the fact, but in some specific cases, this is necessary.
-
Execute on Aggregations: This option is available only for In-Memory formulas. When this property is active, the formula's calculation will be performed at aggregated levels.
-
Execute on Leaves: This option is available only for In-Memory formulas. When this property is active, the formula's calculation will be performed at leaf levels.
-
Live Calculation: When this property is active, the formula's calculation will be performed whenever a premise is altered by the user, without the need to save the data.
¶ 3.2.4. Description
Allows describing the purpose or objectives of the formula, providing a detailed explanation of the formula.
¶ 3.2.5. Data
Allows viewing information about the formula's execution scope, making it possible to analyze each expression generated by the formula and visualize data for these expressions. In the data tab, we have Expression, Distinct, and Result;
-
Expression: In Expression, we can see the formula's execution scope, displaying each premise separately;
-
Distinct: In Distinct, we can view the unique combination of members altered by the expression. In Distinct, we will have Account, Time, Scenario, and Entity, allowing us to see where data is being altered by that expression;
-
Result: In Result, we can preview the data in the model based on the selected expression;
¶ 3.3. Creating a Formula
To create a new formula, follow these steps:
- In the main T6 menu, double-click the Explorer option;
- Select the location where the new formula will be created;
- After selecting the location, in the ribbon, click New Item, click Modeling, and select the Formula option;
- A new formula will be displayed in the listing. Select the created formula and click
; - A panel with 4 tabs (Formula, Scope, Description, and Data) will open on the side;
- In the Formula tab, the following fields will be available for completion:
- Application: Indicates the application where the formula was created;
- Type: Indicates the formula type (Special or In-Memory);
- Formula Group: Optional field where we can select a group for the formula;
- Position: Indicates the formula's position relative to others. The positioning refers to the order of formula execution;
- Formula Name: Mandatory field where we must enter the formula's name;
- Expression: Mandatory field where we must specify the formula's details;
- If necessary,
use autocomplete by clicking CTRL + SPACE;
- The function icon enables viewing the available functions for use in the expression;
7. After completing the fields in the Formula tab, navigate to the Scope tab;
8. In the Scope tab, it is possible to filter the execution context of the formula. Here we can:
- Enable Execute Automatically, to calculate the formula upon saving data in a form;
- Enable Execute Only in Planned, to calculate only in the planned scenario;
- Enable Execute Only in Actual, to calculate only in the actual scenario;
- Enable Allow Persist Zero, so the formula saves the value zero in the fact;
- Enable Execute on Aggregations, to calculate the formula on aggregated levels;
- Enable Execute on Leaves, to calculate the formula on leaf levels;
- Enable Live Calculation, so the formula is recalculated whenever its premise is altered by the user;
- Navigate to the Description tab;
- In the Description tab, you can detail the formula's purpose and objectives;
- Navigate to the Data tab;
- In the Data tab, it is possible to analyze information about the formula's execution scope, making it possible to view data affected by the formula and analyze where the expressions of the formula will be altered.
- Click the Save button to save the changes made to the formula.
¶ 3.4 Accessing Application Formulas
The list of existing formulas in the application can be accessed from the main T6 Planning screen, through Menu -> Modeling -> Formulas.
The system will display the Formulas screen, which contains the list of all formulas in the application.
¶ 3.5 Configuration and Use of Features
Formula configuration is done from the Formulas screen.
This screen contains various features and, among other functions, allows you to sort, add, edit, remove, and execute formulas, as well as view cycles or intersections in the formulas.
For a better understanding, observe the numbered and highlighted features in the image below, and then follow the details and use of each feature.
-
Formula List: This is a grid that displays the list of existing formulas in the model and contains the following columns:
- Pos: Indicates the position of the formula in relation to others. The position refers to the order in which the formulas will be executed.
- Type: Indicates the type of the formula (Special or In-Memory).
- Group: Formula group.
- Name: Name that identifies the formula.
Configuration of the list and use of features:
- To configure a specific formula, double-click on it in the formula list. The Formula Creation and Editing screen will be displayed. For more details, see the Create and Edit Formulas section in this Manual.
- To use the features located at the bottom of the screen, where the Action Menu and Action Buttons are, it is necessary to pre-select a specific formula in the list by clicking once on it. Then click on the desired feature.
- To use the features of the Tools Menu, located at the top of the screen, there is no need for a pre-selection in the formula list, as the use of such options affects all formulas in the list.
-
Type: This is a combobox that allows filtering formulas based on the selected type, displaying only formulas related to the selected group.
-
Formula Group: This is a combobox that allows filtering the list of formulas based on the selected Group, displaying only formulas related to the selected group.
-
Application: This is a combobox that allows filtering formulas based on the selected application, displaying only formulas related to the selected application.
-
Automatically Execute Formulas: This is a checkbox, which should be unchecked when you want to temporarily disable the automatic execution of formulas. The use of this feature affects the entire formula list.
-
Action Buttons: These are the buttons located in the bottom right corner of the screen.
Use of the buttons:
- The use of these buttons implies pre-selecting an item in the formula list by clicking once on a specific formula and then clicking on the desired button.
Button options:
- Add: Allows creating and adding a new formula to the existing list. Clicking the button will display the Formula Creation and Editing screen. For more details, see the Create and Edit Formulas section in this manual.
- Edit: Allows editing an existing formula in the list. Clicking the button will display the Formula Creation and Editing screen. For more details, see the Create and Edit Formulas section in this manual.
- Remove: Allows removing a previously selected formula in the list.
- History: Displays all executions of a previously selected formula in the list. For more details, see the Model History section in the Formula Execution chapter of this manual.
-
Actions Menu: This is the options menu located in the bottom right corner of the screen.
Options in the actions menu:
- Move Up: Allows moving a previously selected formula in the list to a position above the current one.
- Move Down: Allows moving a previously selected formula in the list to a position below the current one.
-
Tools Menu: This is the options menu located in the top right corner of the screen. The tools menu contains features that, when used, automatically affect the entire content of the formula list. Therefore, there is no need to pre-select a specific formula in the list.
Options in the tools menu:
- Reorder: Allows automatically reordering all formulas, taking into account their precedence and informing whenever a circular reference occurs.
- Execute Special Formulas: Allows executing one or more formulas. Clicking on this option will initiate the Special Formulas Execution Assistant. For more details, see the Special Formulas Execution section in the Execution chapter of this manual.
- Cycles: Displays formulas that have cycles. For more details, see the Cycles section in the Performance chapter of this manual.
- Intersections: Displays formulas that have intersections. For more details, see the Intersections section in the Performance chapter of this manual.
¶ 3.6 Create and Edit Formulas
Use this functionality to create a new formula or modify the properties of an existing formula.
- To Add Formula: On the Formulas Sorting screen, use the Add button located in the bottom right corner of the screen.
- To Edit Formula: On the Formulas Sorting screen, select the formula you want to edit by clicking on it in the list of formulas. Then click the Edit button located in the bottom right corner of the screen.
The Add and Edit buttons are highlighted in the image above. Clicking the Add or Edit buttons will display the Formula screen. This screen includes the 4 configuration steps of the formula, as highlighted in the image below.
To start the configuration, for both Adding and Editing a formula, use the following steps:
On the Formula screen – FORMULA Tab:
-
Application: Indicates in which application the formula was created.
-
Type: Indicates the type of the formula.
-
Formula Group: Optional field, where you can select a group for the formula.
-
Position: Indicates the position of the formula in relation to others. The position refers to the order in which the formulas will be executed.
-
Formula Name: Mandatory field, where you should enter the name of the formula.
-
Functions: Its use is linked to the Expression field. By clicking on this field, the Functions List screen will be displayed. You can select a function based on your needs and its description. For more details about existing functions, refer to the Functions chapter in this manual.
-
Expression: Mandatory field, where the details of the formula should be provided. Note some points that will facilitate the detailing:
- If you want to choose a function to fill in the formula expression, simply click the Functions button, located in the upper right corner of the screen. Check the list of available functions in the Functions chapter.
- If you need help filling in the formula text, you can use the autocompletion assistant. To do this, press Ctrl + space and view possible alternatives based on the current context of the expression. Note that:
- Search Function: Allows you to choose a function to use in the formula. By choosing this option, you will be directed to a list of available functions. After choosing the desired function, you will be directed to the "Function Editing" screen that allows you to edit the parameters of a function.
- Member Scope Editing: Allows editing the member scope.
- Search Member or Dimension: Allows searching for a member or dimension.
In the image below, the red highlights point to mandatory fields. The blue highlight shows the autocompletion assistant for the Expression field.
Note that every new formula receives a temporary automatic positioning. After publishing the Cube, T6 Planning will adjust the positioning of all formulas.
To finish, click OK. The list of formulas will be updated.
On the Formula screen – SCOPE Tab:
- Check the checkbox corresponding to the type of filter with which you want to execute the formula. For more details on filters, see the Scope topic in the Formulas chapter of this manual.
- Click on the dimension row to select it. Then click the Edit button and select the members that will compose the scope. Thus, the list will show the Dimension Name, Scope (or Members), and Selected Attributes.
On the Formula screen – DESCRIPTIVE Tab:
- In the Descriptive field, describe the purpose of the formula or its objectives.
On the Formula screen – DATA Tab:
- On this screen, you only view information related to the formula execution scope. It is possible to analyze each expression generated by the formula and visualize data from these expressions. This screen is widely used to identify possible errors or issues in formula calculations. For more details, see the Identifying Problems in Cycles topic in the Performance chapter of this manual.
- The Expression field displays a list of all expressions of operands and operand blocks of the formula. For example, in a formula A = B + C, A, B, and A + B will be displayed in the list.
- The Distinct field displays a list of distinct values of tuples where there is data for the selected expression.
- The Result field displays a list of values found in various tuples for the selected expression.
¶ 3.6.1 Duplicate a Formula
To facilitate copying formulas without altering the original structure, T6 Planning provides a button that allows a new formula to be created identical to the current one. The Duplicate button allows replicating a formula and optimizing the formula creation time.
- In the list of formulas, click on the formula you want to duplicate, and in the options bar, click the Duplicate button.
- The duplicated formula will be displayed in the list of formulas. Rename it.
¶ 3.7 Create and Edit Formula Group
Use this functionality to create groups of formulas and better organize formulas by context, categories, or purpose.
To add a Formula Group:
-
In the T6 Planning menu, access Explorer -> Browser.
-
On the left side of the screen, you will find the list of system folders.
-
Select the folder where you want to save the Formula Group.
-
In the options bar, click New Item, expand the Modeling item by clicking on it, and then click Formula Group.
-
The new Formula Group will appear in the center of the screen, in the list of formula groups.
-
Name the group.
To Edit a Formula Group:
-
In the list of formula groups, select the Formula Group you want to edit by clicking on it.
-
Then, in the options bar, click the Edit button.
-
A pop-up window will be displayed. The application field is already defined, so you need to enter the name of the group being created, with the Name field being mandatory. Then click OK.
¶ 4. Formula Execution
¶ 4.1 Overview
Whenever a change or data insertion occurs, T6 Planning identifies the formulas involved in the calculation of this data and executes them. If there is a dependency between any of these formulas, it will also be executed.
T6 Planning uses this identification system to avoid recalculating all other existing formulas without any data changes.
This chapter will show how cycles, dependencies, parameters, and formula execution work in T6 Planning.
¶ 4.2 Dependencies, Cycles, and Recursions
When the application has various formulas that interact with each other, or when there is a dependency between them, this set of formulas is called a cycle.
When publishing the cube, T6 Planning orders the formulas according to their dependencies. If the formula is part of a cycle, it will remain in the same position relative to the formulas in the cycle. In other words, the formulas within a cycle must be manually ordered.
Formula cycles can be viewed in: -> Sort Formulas window -> Tools -> Cycles. This view is used to check if an unintentional cycle is created and if it is necessary to delete a formula, knowing where it will impact.
¶ 4.2.1 Dependencies between Formulas
The dependency between formulas can be characterized as direct or indirect, depending on the paths between the formulas.
- Direct: When a formula depends on the assumptions of the form after the data is saved to be executed. For example, when entering data in a form that calculates the total monthly expenses.
- Indirect: When the calculation of one result depends on the calculation of another to be executed. For example, a form that calculates the total annual expenses based on the sum of monthly expenses.
¶ 4.2.2 Formula Cycles
A cycle is a group of formulas where their execution can occur with recursion and/or circular reference.
¶ 4.2.3 Formula Recursion
Recursion is a path of mutual dependency within a cycle where variations occur in the dimensions of the model, and where all formulas in the cycle are executed for each leaf member of the dimension, changing only the result in that context.
In the execution of a cycle with recursion, for each leaf member of the dimension, all formulas in the cycle are executed. For example, the Time dimension may reference a previous period, running all the formulas in the cycle month by month and containing calculations where the initial balance of the current month is the result of the final balance of the previous month. The final balance of the current month will be the initial balance plus any movement.
¶ 4.2.4 Formulas with Circular Reference
When one formula depends on another without variation in the dimension, a circular reference occurs. In the execution of a cycle with circular reference, all formulas in the cycle are executed N times, where N is defined in the application parameter table, through the MaximumFormulasIteration parameter, which defaults to 1.
This parameter specifies the maximum number of iterations used to solve formulas with circular reference. As long as the resulting values continue to change with each execution, T6 Planning will continue to iterate; therefore, it is ideal for this parameter to remain at the default value of 1, as in most cases, a single iteration is sufficient for T6 Planning to resolve the circular reference.
For example: in the case of the Time dimension, when all formula calculations occur within the same month. In this case, the system runs the formulas for all months multiple times as the interactions occur.
¶ 4.3 Formula Execution Assistant
It is the functionality that allows manually executing one or more formulas of the model. To use it, follow the steps below.
On the Formulas screen:
- In the top right corner of the screen, in the Tools menu, click on the Execute Special Formulas option.
In the Special Formulas Execution Assistant screen – FORMULAS step:
- If you want to display only the formulas from a specific group, select it in the Group dropdown.
- The Available Special Formulas field displays the list of formulas available for execution.
- The Selected Special Formulas field displays the list of formulas selected for execution.
- To add a formula, click on it and use the central movement buttons, which allow you to add and remove a selected formula, or all formulas at once.
- Check the Insert References checkbox if you want to insert, at runtime, formulas that reference the selected formulas. Thus, if the user forgets or does not add such formulas to the execution list, T6 Planning will automatically insert them for consistency in the calculated values.
- To proceed, click Next.
In the Special Formulas Execution Assistant screen – EXECUTION SCOPE step:
- If you want to restrict the execution scope of the formula, select it from the list of each respective dimension of the model.
- To configure the scope of a specific dimension, select it and then click the Edit button, in the bottom right corner of the grid.
- To proceed, click Next.
In the Special Formulas Execution Assistant screen – SUMMARY step:
- Confirm the selected formulas and their execution scope.
- To proceed, click Finish.
In the Special Formulas Execution Assistant screen – FINISHED step:
- This last step will display the message that the formulas were executed successfully.
- To open details about the execution of a formula, simply select it in advance in the list and click the View Details button, in the bottom right corner of the screen.
¶ 4.4 Model History
This feature displays a list of executions for a specific formula, allowing analysis to check which formulas are consuming more time.
On the Formulas screen:
- To view the executions of a formula, select it from the list and then click on the History button located in the bottom right corner of the screen.
- A popup will open showing all the formula executions.
On the Formula Execution screen, a list displays the following details in columns:
- Start: Displays the date and time when the formula started its execution.
- End: Displays the date and time when the formula finished its execution.
- User: Indicates the user who executed the formula.
- Rows: Informs the total number of database rows that were modified by the formula.
- Times: Indicates the total execution time of the formula.
To view more details about a specific execution, click the View Details button on the desired row.
A popup with more execution details will open.
It is important to note that if the EnableLogs parameter is not enabled, only the last execution of the formula will be displayed because records of previous executions will not be retained. For more details on T6 Planning parameters, refer to the STD Parameters Setup Manual.
¶ 5. Performance
¶ 5.1 Overview
This chapter covers some important points that will help you achieve maximum performance when using formulas in T6 Planning.
¶ 5.2 Creation
For better formula performance, follow these instructions:
- Try to write formulas in the simplest way possible.
- Whenever possible, use available functions.
- If an expression is repeated in various formulas, create a formula with just that expression and use it when needed.
- Avoid creating cycles with unnecessary recursions and circular references.
¶ 5.3 Identifying Bottlenecks
To identify bottlenecks in formula implementation, identify the form that is experiencing slowness when saving data and follow these steps:
-
Clear the formula log table with the following SQL:
TRUNCATE TABLE REP_LOGFORMULA
-
Save data in the form that is experiencing slowness and check the total save time in seconds.
-
To know the formula save time in seconds, execute the following SQL:
SELECT SUM((DATEDIFF(MS, datExecutionStart, datExecutionEnd) / 1000)) as Duration FROM REP_LOGFORMULA
-
To know the total formula execution time in seconds, execute the following SQL:
SELECT (DATEDIFF(MS, (SELECT MIN(datExecutionStart) FROM REP_LOGFORMULA), (SELECT MAX(datExecutionEnd) FROM REP_LOGFORMULA)) / 1000) as Duration
-
To know the execution time of each formula in seconds, execute the following SQL:
SELECT (DATEDIFF(MS, datExecutionStart, datExecutionEnd) / 1000) as Duration, dscFormulaName FROM REP_LOGFORMULA ORDER BY Duration DESC
-
Divide the value found in step five by the value found in step three and multiply the result by one hundred. This value represents the percentage of time used with formulas. If this percentage is very close to one hundred, check in the result of step five which formulas are contributing more to the performance issue.
-
Divide the result of step four by step five and multiply the result by one hundred. This value represents the percentage of SQL Server execution in relation to the total formula execution time. If this value is not close to one hundred, the application may be experiencing performance problems.
¶ 5.4 Identifying Problems in Cycles
To help identify specific problems in cycles, we will describe some occurrences and how to solve them.
-
Incorrect calculation in a cycle with recursive formulas month by month
- Situation: When a specific form containing a cycle with recursive formulas incorrectly calculates a cumulative field, such as the calculation of the initial balance for a month based on the result of the final balance of the previous month.
- Solution: Check the formula list to see if any of them are ordered incorrectly, such as the formula that calculates the initial balance being positioned after the one that calculates the final balance, and if so, reorder it manually, as the system does not reorder formulas that are in cycles. After reordering, a new execution of the formulas should be performed.
- Example: A form for entering monthly income and expenses where, at the end of each month, the Final Balance is calculated based on these entries. This result will carry over to the next month, forming the Initial Balance. When the above problem occurs, the Initial Balance is calculated incorrectly. After applying the suggested solution, the calculation of the Initial Balance will be successful.
-
Incorrect calculation in a cycle with recursive formulas dependent
- Situation: When a specific form containing a cycle with recursive formulas incorrectly calculates the result of a movement that depends on another, within the same period.
- Solution: Check the list of movements (Accounts) to see if the account that depends on the result of the other is ordered incorrectly, that is, positioned before it. If so, reorder them manually, placing the account that will calculate the first result first, and then the account that depends on this result.
- Example: A form that calculates the total monthly expenses by adding fixed expenses to the calculation of weekly expenses. In this case, the calculations are based on movements or Accounts, with weekly expenses as Movement 1 and monthly expenses as Movement 2. When these accounts are ordered incorrectly with the inverted position, when the system executes the formulas, only the value of Movement 1 will be calculated. When running the formulas a second time, the system will calculate correctly because, in this case, the model will resolve itself, since the value of Movement 1 had already been calculated, resulting in the correct calculation of Movement 2. After applying the solution and correctly ordering Movement 2 after Movement 1, the values will be generated correctly whenever the formulas are executed.
-
Incorrect calculation in a cycle with circular reference
- Situation: When a form calculates different values with each formula execution.
- Solution: This type of occurrence is a circular reference, where the calculation of the value of a member depends on the result of the parent member, that is, itself. To fix this problem, it is necessary to edit the formula and replace the account with a child member of the resulting account.
- Example: A form that calculates a percentage of the Investment account using the formula: Investment = Revenue + 0.01. In this case, the Revenue account is the parent member of the Investment account, and in this formula, the calculation of Investment depends on the result of the parent member. After applying the suggested solution and replacing the Investment account with a child member of the Revenue account, such as the Product account, the circular reference will be broken so that, when performing the calculation, the system will be breaking down a parent account into its child accounts.
¶ 5.5 Identifying Other Issues
To check if the slow saving of the form is associated with formulas:
-
Once identified which formulas are experiencing performance issues, check if there is a better way to perform the same calculation.
-
Check if there is any intersection interfering with the performance of the calculations. Intersections are occurrences where two formulas or expressions are calculating the same tuple, which cannot occur under any circumstances, as this generates recalculation, database overload, and performance degradation.
Whenever necessary, you can access the Formulas Sort screen - Intersections; check if there is any intersection created by mistake, such as a duplicate formula, and remove it.
¶ 6. Functions
¶ 6.1 Overview
This chapter covers the functions available in the application and their purposes.
¶ 6.2 Accessing the List of Functions
To access the list of functions in the main menu of T6 Planning, go to Modeling -> Formulas.
The Formulas screen will be displayed. Click on the Add button at the bottom right of the screen.
- On the formula tab, click on the Functions button (highlighted on the right).
The screen displaying the List of Functions for the application will be shown. You can navigate between the available functions and view their descriptions. After selecting the desired function, click the Ok button.
¶ 6.3 Parameter Types
In this field, the Parameter Types used in T6 Planning functions will be presented.
¶ 6.3.1 Attribute
A characteristic applicable to members of the dimension.
Example:
¶ 6.3.2 Block
A set of elements to be calculated.
Example:
(2 + 3 + 4) = Numeric block
¶ 6.3.3 Column
A column of a database table.
Example:
¶ 6.3.4 Comparator
An expression that compares two values using the signs below and returns true or false.
= equal to
!= not equal to
greater than
= greater than or equal to
< less than
<= less than or equal to
Example:
¶ 6.3.5 Conditional
Conditional to be tested. This parameter must be a comparison between two operands. Its syntax should be an operand followed by a comparator followed by an operand.
The operand must be:
- Member, which may or may not have scope
- Number
- Constant
- Property of a member
Example:
¶ 6.3.6 Dimension
A dimension is an organized hierarchy of categories or members. Dimensions represent elements of the company.
Example: Elements can be tangible, such as Employees, Products, or Accounts, or they can represent important views of business data, such as Business Processes, Time, and Scenario.
¶ 6.3.7 Hierarchy
Groupings of different members belonging to the same dimension.
Example:
¶ 6.3.8 Member
A member of the dimension is a single position or item in a dimension. The set formed by one member from each dimension represents a context in the fact table.
Example:
¶ 6.3.9 Scoped Member
It is a set of members, one from each dimension.
Example:
¶ 6.3.10 Level
Represents the hierarchical level among members of the same dimension. Being Parent (Level 1), Child (Level 2), Grandchild (Level 3), etc.
Example:
¶ 6.3.11 Number
Mathematical concept for representing measurement, order, or quantity.
Example:
¶ 6.3.12 Integer
Integers consist of all natural positive, negative numbers, and zero.
Example: Z = {...,−1, −2, −3,−4 ...} or
¶ 6.3.13 Positive Integer
Positive integers consist of natural positive numbers, including zero.
Example: Z+ =
¶ 6.3.14 Table
Database Table.
Example:
¶ 6.3.15 Value
Integers or decimals.
Example:
¶ 7 Available Functions
In this section, the functions available in T6 Planning, their description, syntax, and parameters will be listed.
¶ 7.1 Absolute
Description:
Mathematical function that results in the absolute value (or modulus) of a number. This value is the number without its sign.
Syntax:
ABS(Item)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Base value to be adjusted | Member, Scoped Member, Block | Yes | 1 |
Example:
| Formula | Description | Result |
|---|---|---|
| =ABS(2) | Absolute value of 2 | 2 |
| =ABS(-2) | Absolute value of -2 | 2 |
¶ 7.2 Adjust
Description:
Adjusts a value.
Syntax:
Adjust(Item, Percentage, Attribute)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Base value to be adjusted | Member, Scoped Member, Block | Yes | No |
| Percentage | Adjustment to be applied | Member, Scoped Member | Yes | No |
| Attribute | Dimension attribute | Dimension, Attribute, Value | No | Yes |
¶ 7.3 Between
Description:
Returns values between two dates.
Syntax:
Between(Value, Start Date, End Date)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Value | Member, Scoped Member | Yes | No |
| Start Date | Start date | Member, Scoped Member | Yes | No |
| End Date | End date | Member, Scoped Member | Yes | No |
Example: To return the salary between the hiring date and the termination date.
¶ 7.4 Cartesian
Description:
Creates a Cartesian product.
Syntax:
Cartesian(Item, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item to create the Cartesian product | Member, Scoped Member, Block | Yes | No |
| Dimension | Dimension | Dimension | Yes | Yes |
¶ 7.5 Ceiling
Description:
The Ceiling function returns an integer value representing the smallest integer that is greater than or equal to the value.
Syntax:
Ceiling(Number, Precision)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Number | Number to be rounded | Member, Scoped Member, Block | Yes | No |
| Precision | Precision value | Integer | No | No |
¶ 7.6 CGrow
Description:
Compoundly adds a percentage to a number in each period.
Syntax:
CGrow(Start, Grow, % Growth Rate)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Start | Initial value to be added to the result | Member, Scoped Member | Yes | No |
| Grow | Incremental value | Member, Scoped Member | Yes | No |
| % Growth Rate | Percentage to grow | Member, Scoped Member | Yes | No |
Example: 1000 growing 10% per month results in 1100, 1210, 1331, and so on.
¶ 7.7 Closing
Description:
Calculates the final balance from the initial balance plus incoming and outgoing movements.
Syntax:
Closing(Opening, In, Out)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Opening | Initial balance | Member, Scoped Member | Yes | Yes |
| In | Incoming movements | Member, Scoped Member | Yes | Yes |
| Out | Outgoing movements | Member, Scoped Member | No | Yes |
¶ 7.8 ClosingVariance
Description:
Calculates the final balance from the initial balance plus incoming and outgoing movements, considering a rate.
Syntax:
ClosingVariance(Opening, Opening In %, Divider Output, In, Out)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Opening | Initial balance | Member, Scoped Member | Yes | No |
| Opening In % | Percentage of incoming balance | Member, Scoped Member, Number | Yes | No |
| Opening Out % | Percentage of outgoing balance | Member, Scoped Member, Number | Yes | No |
| Divider Output | Output divisor | Member, Scoped Member, Number | Yes | No |
| In | Incoming movements | Member, Scoped Member | Yes | Yes |
| Out | Outgoing movements | Member, Scoped Member | No | Yes |
¶ 7.9 CumAdjust
Description:
Used when we need to accumulate a value but need to apply an operation to this value (addition or multiplication), not simply accumulate; For performance reasons, we will use CumAdjust.
A table is created at runtime when executed, some necessary values are inserted, and then it is run in a CTE (Common Table Expression) with no recursion limit, to retrieve the value and execute.
Syntax:
[Function Result] = CumAdjust([Increment], [Multiplier], [Increment After Multiplier], [Restart])
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Increment | Increment in value | member, scoped member | Yes | No |
| Multiplier % | Multiplier applied to the accumulated value | member, scoped member, number | No | No |
| Increment After Multiplier | Increment in value after multiplication | member, scoped member, number | No | No |
| Restart | Restarts the accumulated value | member, scoped member | No | No |
Possible Results (Expressions):
- Result with Increment:
[Function Result With Increment] = CumAdjust([Increment]) - Result with Increment and Multiplier:
[Function Result With Increment and Multiplier] = CumAdjust([Increment], [Multiplier]) - Result with Increment, Multiplier, and Increment After Multiplication:
[Function Result With Increment and Multiplier and Increment After Multiplier] = CumAdjust([Increment], [Multiplier], [IncrementAfterMultiplier %]) - Final Result:
[Function Result] = CumAdjust([Increment], [Multiplier %], [IncrementAfterMultiplier], [Restart])
Click here to view examples:
Result with Increment
| Data | Description | |
|---|---|---|
| 100 | Base Value (Accumulated Value) | |
| 100 | Increment | |
| Formula | Description | Result |
| [Accumulated] = CumAdjust([Increment]) | Receives the base value and adds the increment | 200 |
Result with Increment and Multiplier
| Data | Description | |
|---|---|---|
| 100 | Base Value (Accumulated Value) | |
| 100 | Increment | |
| 10 % | Multiplier % | |
| Formula | Description | Result |
| [Accumulated] = CumAdjust([Increment], [Multiplier]) | Receives the base value, adds the increment and applies the multiplication |
220 |
Result with Increment After Multiplication
| Data | Description | |
|---|---|---|
| 100 | Base Value (Accumulated Value) | |
| 100 | Increment | |
| 10 % | Multiplier % | |
| 100 | Increment after multiplication | |
| Formula | Description | Result |
| [Accumulated] = CumAdjust([Increment], [Multiplier], [IncrementAfterMultiplier %]) | Receives the base value, adds the increment, applies the multiplication, and adds the increment after multiplication. |
320 |
Result with Increment After Multiplication and Restart
| Data | Description | |
|---|---|---|
| 100 | Base Value (Accumulated Value) | |
| 100 | Increment | |
| 10 % | Multiplier % | |
| 100 | Increment after multiplication | |
| 0 | Restart | |
| Formula | Description | Result |
| [Accumulated] = CumAdjust([Increment], [Multiplier %], [IncrementAfterMultiplier], [Restart]) | Receives the base value, adds the increment, applies the multiplication, and adds the increment after multiplication. |
320 |
| Next calculation - (The Restart value will replace the accumulated value making the next period start with the informed value) |
0 |
Example of Restart Application:
The CARestart value replaces the accumulated value in the current calculation, causing the next calculation to start with the accumulated value informed in CARestart.
¶ 7.10 Cumulate
Description:
Accumulates the base value up to the current period.
Syntax:
Cumulate(Value)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to be accumulated | Member, Scoped Member | Yes | No |
¶ 7.11 Delay
Description:
Calculates receipts or payments based on the percentages for each period.
Syntax:
Delay(Input, % Period)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Input | Invoices | Member, Scoped Member | Yes | No |
| % Period | Percentage of the period | Member, Scoped Member, Number | Yes | Yes |
Example: An invoice of 1000 will be paid 30% upfront, 30% in the next month, and 40% in the second month. The delay function will calculate the payment amounts for each month (300 in the same month as the invoice, 300 in the month following the invoice, and 400 in the third month from the invoice).
¶ 7.12 Depr
Description:
Calculates the linear depreciation of an asset based on its lifespan. The depreciable value is equal to the asset value divided by its lifespan.
Syntax:
Depr(Capitalization, Life)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Capitalization | Value of the asset to be depreciated | Member, Scoped Member | Yes | No |
| Life | Lifespan of the asset | Member, Scoped Member | Yes | No |
¶ 7.13 Descendants
Description:
Returns the value of the member without the value of the given member.
Syntax:
Descendants(Member)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Member | Member used to fetch the descendants | Member, Scoped Member | Yes | No |
¶ 7.14 DiffFirstValue
Description:
Finds the difference of the current member with the first value of the member in the hierarchy, considering vertical and horizontal displacement.
Syntax:
DiffFirstValue(Item, Parent, Lag, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item you want to find the first value for | Member, Scoped Member, Block | Yes | No |
| Parent | Vertical displacement | Positive Integer | Yes | No |
| Lag | Horizontal displacement | Integer | Yes | No |
| Dimension | Dimension you want to find the first value for | Dimension, Hierarchy | No | No |
¶ 7.15 DiffLastValue
Description:
Finds the difference of the current member with the last value of the member in the hierarchy, considering vertical and horizontal displacement.
Syntax:
DiffLastValue(Item, Parent, Lag, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item you want to find the last value for | Member, Scoped Member, Block | Yes | No |
| Parent | Vertical displacement | Positive Integer | Yes | No |
| Lag | Horizontal displacement | Integer | Yes | No |
| Dimension | Dimension you want to find the last value for | Dimension and Hierarchy | No | No |
Example: Formula: (Base, period n) - (Base, period n -1)
¶ 7.16 DiffPercent
Description:
Calculates the percentage difference between the current period and the previous period.
Syntax:
DiffPercent(Value, Step)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the difference | Member, Scoped Member | Yes | No |
| Step | Step to calculate the difference | Member, Scoped Member, Integer | No | No |
¶ 7.17 DiffProportion
Description:
Calculates the proportional difference between the current period and the previous period.
Syntax:
DiffProportion(Value, Step)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the difference | Member, Scoped Member | Yes | No |
| Step | Step to calculate the difference | Member, Scoped Member, Integer | No | No |
¶ 7.18 DiffValue
Description:
Calculates the value difference between the current period and the previous period.
Syntax:
DiffValue(Value, Step)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the difference | Member, Scoped Member | Yes | No |
| Step | Step to calculate the difference | Member, Scoped Member, Integer | No | No |
¶ 7.19 Expand
Description:
Expands the item to a specified level.
Syntax:
Expand(Item, Level Start, Level End)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item to be expanded | Member, Scoped Member, Block | Yes | No |
| Level Start | Starting level of the dimension | Dimension and Level | Yes | Yes |
| Level End | Final level of the dimension | Dimension and Level | Yes | Yes |
¶ 7.20 Filter
Description:
Filters the item by the values of the dimension attribute.
Syntax:
Filter(Item, Attribute)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item to be expanded | Member, Scoped Member, Block | Yes | No |
| Attribute | Dimension attribute values for filtering | Dimension and Level | Yes | Yes |
¶ 7.21 First Value
Description:
Fetches the first hierarchy value, considering vertical and horizontal displacement.
Syntax:
FirstValue(Item, Parent, Lag, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item you want to find the first value for | Member, Scoped Member, Block | Yes | No |
| Parent | Vertical displacement | Positive Integer | Yes | No |
| Lag | Horizontal displacement | Integer | Yes | No |
| Dimension | Dimension you want to find the first value for | Dimension and Hierarchy | No | Yes |
¶ 7.22 Floor
Description:
The floor function returns an integer value representing the largest integer that is less than or equal to the given value.
Syntax:
Floor(Number, Precision)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Number | Number to be rounded | Member, Scoped Member, Block | Yes | No |
| Precision | Precision value | Integer | No | No |
¶ 7.23 Group
Description:
Groups the item by dimension levels.
Syntax:
Group(Item, Level)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item to be grouped | Member, Scoped Member, Block | Yes | No |
| Level | Dimension level | Dimension and Level | Yes | Yes |
¶ 7.24 HasValue
Description:
Returns 1 where it has a value.
Syntax:
HasValue(Value)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Value to be checked | Member, Scoped Member | Yes | No |
¶ 7.25 IIF
Description:
Checks if a condition is satisfied, returning one value if true or another value if false.
Syntax:
IIF(Conditional, True, False)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Conditional | Condition to be tested | Comparator | Yes | No |
| True | Value if the condition is true | Member, Scoped Member, Number, Conditional | Yes | No |
| False | Value if the condition is false | Member, Scoped Member, Number, Conditional | No | No |
Comments: Not all possible combinations of operands and comparators are valid. Below is the list of valid combinations:
- Member, Less than or Equal, Member
- Member, Greater than or Equal, Member
- Member, Not Equal, Member
- Member, Equal, Member
- Member, Less than, Member
- Member, Greater than, Member
- Member, Less than or Equal, Number
- Member, Greater than or Equal, Number
- Member, Not Equal, Number
- Member, Equal, Number
- Member, Less than, Number
- Member, Greater than, Number
- Number, Less than or Equal, Member
- Number, Greater than or Equal, Member
- Number, Not Equal, Member
- Number, Equal, Member
- Number, Less than, Member
- Number, Greater than, Member
- Member, Less than or Equal, Property
- Member, Greater than or Equal, Property
- Member, Not Equal, Property
- Member, Equal, Property
- Member, Less than, Property
- Member, Greater than, Property
- Property, Less than or Equal, Member
- Property, Greater than or Equal, Member
- Property, Not Equal, Member
- Property, Equal, Member
- Property, Less than, Member
- Property, Greater than, Member
- Number, Less than or Equal, Property * **
- Number, Greater than or Equal, Property * **
- Number, Not Equal, Property * **
- Number, Equal, Property * **
- Number, Less than, Property * **
- Number, Greater than, Property * **
- Property, Less than or Equal, Number * **
- Property, Greater than or Equal, Number * **
- Property, Not Equal, Number * **
- Property, Equal, Number * **
- Property, Less than, Number * **
- Property, Greater than, Number * **
- Constant, Not Equal, Property * ***
- Constant, Equal, Property * ***
- Property, Not Equal, Constant * ***
- Property, Equal, Constant * ***
* Valid only if the True (and False if exists) parameters are members.
** Valid only if the property type is compatible with a number.
*** Valid only if the property type is compatible with text.
¶ 7.26 IPMT
Description:
Returns the interest payment for a specific period of an investment based on periodic, constant payments, and a constant interest rate.
Syntax:
IPMT(Rate, Per, NPer, PV, FV, Type)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Rate | Interest rate | Member, Scoped Member | Yes | No |
| Per | The period for which to find the interest | Member, Scoped Member | Yes | No |
| NPer | Number of payments | Member, Scoped Member | Yes | No |
| PV | Present value or principal value | Member, Scoped Member | Yes | No |
| FV | Future value | Member, Scoped Member | No | No |
| Type | Number 0 or 1 indicating when the payment is due. 0 indicates at the end of the period and 1 at the beginning of the period | Integer | No | No |
¶ 7.27 Lag
Description:
The Lag function calculates payments or receipts by delaying or advancing a number of periods.
Syntax:
Lag(Input, Periods, Padding, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Input | Value to be delayed or advanced | Member, Scoped Member | Yes | No |
| Periods | Number of periods | Member, Scoped Member, Block | Yes | No |
| Padding | Value to add | Member, Scoped Member | No | No |
| Dimension | Dimension to be delayed or advanced | Dimension, Hierarchy | No | No |
Example: If we have a value of 1000 in period 5, the Lag function with a Number of periods 3 will delay the value 1000 to period 8. If the Number of periods were -3, the value 1000 would be advanced to period 2.
¶ 7.28 LastNonEmpty
Description:
Returns the last non-empty value.
Syntax:
LastNonEmpty(Item, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Block used to obtain the last non-empty value | Member, Scoped Member, Block | Yes | No |
| Dimension | Dimension | Dimension | No | No |
¶ 7.29 LastValue
Description:
Fetches the last hierarchy value, considering vertical and horizontal displacement.
Syntax:
LastValue(Item, Parent, Lag, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item you want to find the last value for | Member, Scoped Member, Block | Yes | No |
| Parent | Vertical displacement | Positive Integer | Yes | No |
| Lag | Horizontal displacement | Integer | Yes | No |
| Dimension | Dimension you want to find the last value for | Dimension and Hierarchy | No | No |
¶ 7.30 LGrow
Description:
LGrow linearly adds a percentage to a number in each period.
Syntax:
LGrow(Start, Grow, Base, % Grow Rate)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Start | Initial value to be added to the result | Member, Scoped Member | Yes | No |
| Grow | Added value | Member, Scoped Member | Yes | No |
| Base | Base value to be multiplied by the percentage and added to the result | Member, Scoped Member | Yes | No |
| % Grow Rate | Percentage to grow | Member, Scoped Member | Yes | No |
¶ 7.31 List
Description:
Checks the value of an account with a list and returns a specific value for each item in the list.
Syntax:
List(Test Value, List Item, Value)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Test Value | Value to be tested | Member, Scoped Member | Yes | No |
| List Item | List item | List | Yes | No |
| Value | Value to be used | Member, Scoped Member, Number | Yes | Yes |
Example: In an account with types of benefits, we can map a different value for each benefit.
¶ 7.32 LN
Description:
Calculates the natural logarithm.
Syntax:
LN(Item)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item for which to calculate the natural logarithm | Member, Scoped Member, Block | Yes | No |
¶ 7.33 LoadData
Description:
Loads values from a table.
Syntax:
LoadData(Table, Column Value, Dimension, Dimension Column)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Table | Table used to load values | Table | Yes | No |
| Column Value | Column where the value is located | Column | Yes | No |
| Dimension | Dimension | Dimension | Yes | Yes |
| Dimension Column | Dimension column | Column | Yes | Yes |
¶ 7.34 Log
Description:
Calculates the logarithm.
Syntax:
Log(Item, Base)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item for which to calculate the logarithm | Member, Scoped Member, Block | Yes | No |
| Base | Base to be used in the logarithm | Number, Member, Scoped Member, Block | Yes | No |
¶ 77.35 Log10
Description:
Calculates the logarithm with base 10.
Syntax:
Log10(Item)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item for which to calculate the logarithm with base 10 | Member, Scoped Member, Block | Yes | No |
¶ 7.36 LookupProperty
Description:
Looks up the value of a property for a block.
Syntax:
LookupProperty (Context, Property, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Context | Context from which to retrieve the property value | Member, Scoped Member, Block | Yes | No |
| Property | Property for which to retrieve the value | Dimension, Attribute | Yes | No |
| Dimension | Dimension to save the value | Dimension | Yes | No |
¶ 7.37 Mapping
Description:
Maps data from a table.
Syntax:
Mapping(Table, Dimension, Source Column, Destination Column)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Table | Table to be mapped | Table | Yes | No |
| Dimension | Dimension to be mapped | Dimension | Yes | Yes |
| Source Column | Source Column | Column | Yes | Yes |
| Destination Column | Destination Column | Column | Yes | Yes |
¶ 7.38 MAvgAbsCenter
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going one period forward and one period backward.
Syntax:
MavgAbsCenter(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the absolute average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member | Yes | No |
¶ 7.39 MAvgAbsLast
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going to previous periods.
Syntax:
MavgAbsLast(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the absolute average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Positive Integer, Member, Scoped Member | Yes | No |
¶ 7.40 MAvgAbsNext
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going forward.
Syntax:
MavgAbsNext(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the absolute average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Positive Integer, Member, Scoped Member | Yes | No |
¶ 7.41 MAvgCenter
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going one period forward and one period backward.
Syntax:
MavgCenter(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Positive Integer, Member, Scoped Member | Yes | No |
¶ 7.42 MAvgLast
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going to previous periods.
Syntax:
MavgLast(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Positive Integer, Member, Scoped Member | Yes | No |
¶ 7.43 MAvgNext
Description:
Calculates the moving average of the specified periods. The quantity parameter indicates how many periods will be used in the average, starting from the current period and going forward.
Syntax:
MavgNext(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the average | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Positive Integer, Member, Scoped Member | Yes | No |
¶ 7.44 Max
Description:
Identifies the maximum value among various members.
Syntax:
Max(Item, Name)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item for which to find the maximum value | Member, Scoped Member, Block | Yes | Yes |
| Name | Dimension to search for the maximum value | Dimension | No | Yes |
¶ 7.45 Min
Description:
Identifies the minimum value among various members.
Syntax:
Min(Item, Name)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Item for which to find the minimum value | Member, Scoped Member, Block | No | Yes |
| Dimension | Dimension to search for the minimum value | Dimension | Yes | Yes |
¶ 7.46 MMaxCenter
Description:
Calculates the maximum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the maximum value, starting from the current period and going one period forward and one period backward.
Syntax:
MmaxCenter(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the maximum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.47 MMaxLast
Description:
Calculates the maximum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the maximum value in the last periods.
Syntax:
MmaxLast(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the maximum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.48 MMaxNext
Description:
Calculates the maximum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the maximum value in the next periods.
Syntax:
MmaxNext(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the maximum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.49 MMinCenter
Description:
Calculates the minimum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the minimum value, starting from the current period and going one period forward and one period backward.
Syntax:
MminCenter(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the minimum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.50 MMinLast
Description:
Calculates the minimum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the minimum value in the last periods.
Syntax:
MminLast(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the minimum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.51 MMinNext
Description:
Calculates the minimum value of the specified periods. The quantity parameter indicates how many periods will be used in the search for the minimum value in the next periods.
Syntax:
MminNext(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to determine the minimum value | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.52 MSumCenter
Description:
Calculates the moving sum of the specified periods. The quantity parameter indicates how many periods will be used in the sum, starting from the current period and going one period forward and one period backward.
Syntax:
MsumCenter(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the sum | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.53 MSumLast
Description:
Calculates the moving sum of the specified periods. The quantity parameter indicates how many periods will be used in the sum, starting from the current period and going to previous periods.
Syntax:
MsumLast(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the sum | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.54 MSumNext
Description:
Calculates the moving sum of the specified periods. The quantity parameter indicates how many periods will be used in the sum, starting from the current period and going forward.
Syntax:
MSumNext(Value, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to calculate the sum | Member, Scoped Member | Yes | No |
| Quantity | Number of periods | Member, Scoped Member, Positive Integer | Yes | No |
¶ 7.55 NPV
Description:
Calculates the net present value of the specified periods. The quantity parameter indicates how many periods will be used in the sum, starting from the current period and going forward.
Syntax:
NPV(Period, Rate, Value)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Period | Periods | Member, Scoped Member | Yes | No |
| Rate | Discount rate | Member, Scoped Member | Yes | No |
| Value | Series of future cash flows | Member, Scoped Member | Yes | No |
¶ 7.56 Opening
Description:
The Opening function calculates the initial balance of the period based on the closing balance of the previous period.
Syntax:
Opening(Closing)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Closing | Closing balance | Member, Scoped Member | Yes | No |
¶ 7.57 Parent
Description:
Retrieves the value of a parent member in a specific dimension. The quantity identifies how many levels to go up in the hierarchy of the selected dimension.
Syntax:
Parent(Input, Dimension, Quantity)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Input | Value | Member, Scoped Member | Yes | No |
| Dimension | Dimension to ascend in the hierarchy | Dimension and Hierarchy | Yes | No |
| Quantity | Number of levels to ascend | Positive Integer | Yes | No |
¶ 7.58 PMT
Description:
Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax:
PMT(Rate, Nper, PV, FV, Type)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Rate | Interest rate | Member, Scoped Member | Yes | No |
| NPer | Number of payments | Member, Scoped Member | Yes | No |
| PV | Present value or principal amount | Member, Scoped Member | Yes | No |
| FV | Future value | Member, Scoped Member | No | No |
| Type | Number 0 or 1 indicating when the payment is due. 0 indicates at the end of the period, and 1 at the beginning of the period | Integer | No | No |
¶ 7.59 Property
Description:
Retrieves a property of a member.
Syntax:
Property(Property, Context)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Property | Property to retrieve the value | Member, Scoped Member | Yes | No |
| Context | Context to retrieve the value of the property | Member, Scoped Member, Block | Yes | No |
¶ 7.60 Remaining
Description:
Used to determine remaining installments in loan amortization.
Syntax:
Remaining(Item, Interval, Grace Period, Dimension)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Item | Block containing the installment number | Member, Scoped Member, Block | Yes | No |
| Interval | Installment interval | Positive Integer | No | No |
| Grace Period | Grace period | Positive Integer | No | No |
| Dimension | Dimension | Dimension | No | No |
¶ 7.61 Round
Description:
Rounds a number to a specified precision. When the precision is positive, the value is rounded to the number of decimal places of the precision; if the precision is negative, the value is rounded on the left side of the decimal point, according to the precision.
Syntax:
Round(Number, Precision)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Number | Number to be rounded | Member, Scoped Member, Block | Yes | No |
| Precision | Precision value | Integer | Yes | No |
Example: Rounding the Number 748.47 with precision -2 results in 700.00.
¶ 7.62 Sum
Description:
Sums the base value from the first period to the final period.
Syntax:
Sum(Value)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value to sum | Member, Scoped Member | Yes | No |
¶ 7.63 Tier
Description:
Calculates a different percentage for each tier. Taxes are a classic example of Tier calculations.
Syntax:
Tier(Value, % Above, Tier, % Tie)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Value | Base value for calculation | Member, Scoped Member | Yes | No |
| % Above | Percentage applied to the value above the last tier | Member, Scoped Member | Yes | No |
| Tier | Value tier | Member, Scoped Member | Yes | Yes |
| % Tie | Percentage applied to the value tier | Member, Scoped Member | Yes | Yes |
¶ 7.64 YTD
Description:
The YTD function accumulates the base value over periods, starting from the beginning of each year or fiscal year.
Syntax:
YTD(Base)
Parameters:
| Name | Description | Parameter Types | Required | Repetitions |
|---|---|---|---|---|
| Base | Base value to be accumulated | Member, Scoped Member | Yes | No |