Processing Data using Calculation

Objective

After completing this lesson, you will be able to use calculation function in order to conduct both simple and complex calculation approaches.

Calculation

This lesson introduces you to the second processing function within the Universal Model called Calculation.

The Calculation Function

The Calculation Function is a central place for conducting the calculation in the Universal Model.

You use this processing function to enhance the data in a data set with calculated attributes based on predefined rules at runtime. This function includes a parser to detect dependencies between fields used in formulas and to ensure that rules are executed in the correct order internally. Circular dependencies are not allowed. The processed data can then be used for consumption in downstream processes such as allocation.

Each calculation rule semantically defines an if-then statement. The "if" part specifies for which records of the input data the rule is relevant. The "then" part is an action and contains a list of fields and formulas that must be calculated.

A static calculation rule is mandatory to successfully configure a Calculation function. As these rules are configured in the environment, they are transportable to the next tenant together with the environment and can be used during the run of a calculation activity. Changing these static rules would cause a modeling change and reactivation is mandatory for the rule to be available during runtime.

To successfully configure a static calculation rule, ensure the following:

The environment where the model entity is to be created is available. For more information on how to create an environment, see Environment.
At least one other function is available in your environment.
A Calculation function has been created in the Manage Functions application.

Configuration of the Calculation Function

Configuration of the calculation function requires similar steps as the derivation function.

A screenshot of a user interface for a documents calculation application. The interface shows various information about the application, including general information, predecessor functions, successor functions, relations, and output fields. The image also includes a section that describes the rules and sequence of the application.

First, we must specify the Input, which indicates the input function.

After that, we must configure the Rules tab. The Rules tab is where we are telling the system what’s our "if-then" statement.

The image shows a user interface for a documents calculation application. The interface displays information about a rule called R1, including the rule's conditions and assignments. The conditions section describes the field that is considered for conditioning, and the assignments section shows the field and formula used for the calculated value. The image also provides definitions for the Conditions and Assignments concepts to help explain their purpose.

Once we are in the Rules tab, there are two mandatory options to be configured: Conditions and Assignments.

Conditions: In there, we add the field that’s considered for conditioning. For example, if we have the list of all European countries in the Country field in our input table and we want to apply the calculation logic just for Germany and Ireland, we would add the Country field in the conditions tab and select the desired country from the list.
Assignments: In there, we add the field (most likely the key figure field type) where our calculation (formula) logic is written.

Rule 1: In our example, the field called TotalIncome is nothing else but the result of the summation between TaxFreeIncome + CapitalGain + Dividend + OtherIncome (see the Formula box).

Since we can have multiple rules in one calculation function, the option called Ensure Distinct Results is quite important. When the Ensure Distinct Results option is enabled then the result of the current rule is used as the input for the subsequent one.

When this option is disabled, then the input for each rule is actually the original input dataset. Technically, we are performing the Union All join type among different calculation rules.

Rule 2: In the second rule, we have applied sql "case when" statement. With this approach, the user can apply the calculation based on the selection in the sql statement. In our example case when the statement was written in the TaxRate field: "case when TotalIncome > 5000 then 10 else 0 end". With something like this, in the final result, we expect the TaxRate field to be fulfilled in the next manner – whenever value in TotalIncome field exceed 5000 TaxRate will get number 10, whenever TotalIncome is below 5000 TaxRate will get number 0.

The image shows a user interface for configuring a rule called R2. It includes sections for defining the rule's conditions and assignments. The image also provides information about the Ensure Distinct Results feature, explaining how it affects the input data and results of subsequent rules. A specific example is given to illustrate the functionality of this feature.

Here, we have a simple example of the Calculation function. The results of the Rules 1 and 2 are displayed in the following table.

The image displays two sections: Input data and Results of Calculation. The Input data section shows a table with various company information, including their name, year, tax-free income, capital gain, dividend, other income, currency, and whether they are allocated to a target company. The Results of Calculation section displays the calculated values based on the input data.

The field TotalIncome is the output from the Rule 1 and the field TaxRate is the output of the Rule 2.

The image provides guidance for using HANA functions in a document calculation application. It explains the use of the equal sign (=) when using only HANA functions, and cautions about case-sensitive formulas when using fields calculated in previous rules. The image also advises avoiding grouping data when the input Model Entity contains associations or relationships, and instead suggests preparing the data using Model View and View before using it for the calculation.

Continue to quiz