Design Advanced Formulas Using the Visual Tool

Preparation: Specify the Context

You can set filters in the context that applies to and governs all transformations and calculations in the advanced formulas step.

For example, if you set the filters for Account, Entity, and Time dimensions, respectively, as dimension member Total Sales, APJ, and 2019.01, later when you start to design a calculation, the source and target data is by default restricted to the total sales of APJ in 2019 January.

In the filter, you can either select the members to include, or select Parameters and choose a parameter. Parameters let you create prompts, or update several values from one place. To learn how to create parameters, see Creating a Parameter.

Preparation: Define and Use a Variable

There are three different types of variables – float, integer, and variable member.

Float and integer type variables are number variables that can be reused in the calculation. Variable member is a virtual member of a certain dimension that can temporarily store intermediate calculated values to be reused elsewhere, to avoid writing the calculation repeatedly.

When you add a variable member, you need to specify a dimension for it. The variable member only stores values for this specific dimension.

After adding the variables, you can use them in conditions and calculations freely, for example:

Case 1: Reuse a float variable in conditions

Case 2: Use a float variable as target

Case 3: Use a variable member in the source

Case 4: Use a float variable in the source input field

Begin: Set Configuration Options

The configuration settings define some basic settings that apply to all the calculations in the advanced formulas step.

Fiscal Year: This configuration governs the time hierarchy used in the advanced formulas. If turned off, then calendar year hierarchy will be used. If turned on, the fiscal year will be used.

Account Sign Flip: If this option is turned on, all calculations consider the sign value (debit or credit) of each account according to the account type (LEQ, AST, INC, and EXP).

0 for Unbooked Data: Unbooked data refers to cells with empty values. If turned on, all empty cells in the source data to be copied will be treated as value 0 cells.

Design Phase: Define Conditions

You can define a condition for any calculation by first adding a condition, and then dragging corresponding calculations under it.

To define a condition, you can either directly choose dimension members, or choose dimension members sharing a certain dimension attribute.

For example, if you want to add all subsidiary companies using EUR as the currency as a precondition, from the condition filter, first expand the Company Code, and then choose from the expanded list the Currency attribute. A dialog will pop up for you to choose values for the Currency attribute. Choose EUR for the Currency.

Design Phase: Set Dimensions to be Repeated

You can define one or several dimensions in the repeat scope, and then calculations added to the repeat scope will be repeated sequentially for the dimension(s).

Remember not to add too many unnecessary dimensions to the repeat scope; otherwise, when you execute the data action in a story later, it will run slowly.

In addition to that, you can choose to repeat statements only for each dimension combination that has values. To do that, click the button, then in the dialog Select Dimension for Repeat, choose the button and turn on the option Repeat for booked members only.

Design Phase: Write Calculations

Calculation is divided into two parts – Target and Source. Target is the data range to which you plan to write data. Source is to filter a certain range of source data and perform calculations on the data. In both source and target scope, you can select the members to include, or select Parameters and choose a parameter. If the parameter you create is of type number, you can add it as a number to the input field in the source scope.

In the target scope, there are two ways of writing data - overwrite or append. Overwrite mode first only cleans the target scope and updates it with the data from source scope. Append mode appends data from source scope to the existing data in target. To do this, when selecting the target scope, choose the button, and choose either Overwrite or Append for Data Writing.

Besides the input field to which you can add a number or a number variable, you can also add mathematical expressions to your source data such as ABS, LOG, LOG10, SQRT, FLOAT, and INT. The expressions can convert your source data in a mathematical way. For example, if you drag source data into the SQRT expression and the source data returns a total value of 10000, the SQRT will return a square root of the value as 100.

In the source data scope, you can choose to copy or read data from other models besides the default model of the data action. A model is available to be linked as a source data model if:

• The currency conversion setting (enabled or disabled) of the model is the same as that of the data action's default model.

• The Date dimension in the model has the same granularity as the Date dimension in the default model.

• Fiscal Year is either disabled in both this model and default model, or enabled with the same month shift.

After choosing the model, for version dimension and all the other dimensions that exist only in this model but not in the data action's default model, you need to specify one member for each of them. For the dimensions that exist in both the linked model and the default model, you can choose to include or not include it in the source scope.

In both the target and source scope, you can choose Date dimension members by selecting a time scope. For example, if your current Date range is 2019.Jan to 2019.Mar and you choose Previous (3) as the time scope in target, then the data range will be 2018.Oct to 2018.Dec.

For detailed information about the time scope selection functions, refer to the section Time/Period scope selection functions of All Script Formulas and Calculations in Advanced Formulas.

Design Phase: Delete Data Facts

A Delete component clears data facts within a certain data scope that is composed of one or more dimension members. You can directly choose dimension members to be included in the scope or select dimension members by specifying dimension attributes.

For example, if you want to delete all the Entity dimension members defined as Entity attribute values of the InterCompany dimension, follow the steps below:

Please note that you cannot delete data facts for a BPC imported model.

Review Phase: Show or Hide Formulas

After successfully creating the advanced formulas, you can choose to hide the formulas and present the results in a way the financial users are more familiar with. To do this, choose the button and deselect the option Formulas.

Review Phase: Switch to the Script View

If you want to further modify the advanced formulas created via the visual tool using scripts, you can switch from the Visual tab to the Script tab. The advanced formulas you created via the visual tool are automatically translated into scripts.