Formulas

If the value of the KPI you create depends on the values of several other KPIs, you can specify a formula that will be used for calculating the KPI.

There are two ways to create a formula:

manually;
using a wizard.

How to create a formula manually

The formulas are introduced on the Values tab of a KPI page in the Formula field (fig. 1). This field becomes available if in the Value collection method section you select the Script option for Fact Value Collection and Plan Value Collection.

Fig. 1. KPI page. Values tab. Fact Value Collection section. Formula field

In a formula, you can use:

functions;
KPIs;
4 arithmetic operations (addition, subtraction, multiplication division);
brackets;
coefficients.

Here is what a formula looks like in general:

<Function₁>("KPI₁")<arithmetic operation>...< arithmetic operation ><Function_n>("KPI_n").

Instead of a function, you can use a number.

Here is an example of a formula:

Value("Revenues in Branch Office 1")*0,8 + Value ("Revenues in Branch Office №2")*0,2

Primary functions used in formulas

Function	Description
Value	Returns the current KPI value. If this function is used in the Collect Fact Value section, the fact value is returned. If this function is used in the Collect Plan Value section, the plan value is returned.
Plan	Returns the plan value of a KPI.
Fact	Returns the fact value of a KPI.
Sum	Returns the sum of a KPI’s values.
Avg	Returns the average of a KPI’s values.
Max	Returns the maximum value of a KPI.
Min	Returns the minimum value of a KPI.
КоличествоДнейвПериоде	Returns the number of days in the period of the specified KPI

These functions return the value of a KPI in the current period.

To get values from a previous you need to use the Prev prefix. For example, you can use the PrevValue("KPI") function.

To get plan values of a KPI, you need to use the Plan prefix in from of the function’s name. For example, to get a sum of plan values, use the PlanSum ("KPI") function.

To get fact values of a KPI, you need to use the Fact prefix in from of the function’s name. For example, to get a sum of fact values, use the FactSum ("KPI") function.

If you need to check if the data over the period is filled in, use the WM function. For example, when you want to get the sum of a KPI's values in the past quarter, use the PrevSumWM("KPI"), in this case the WM function will check if all the values in the selected period are entered.

You can also use multiple functions.

Using the Formula Wizard

To create a formula with the formula wizard, click Wizard in the Value Collection Method field. This button only becomes available after you have selected the Script option in the Value Collection Method field (fig. 2).

Fig. 2. Value Collection Method field in the Values tab of a KPI page"

The Formula Wizard window will open (fig. 3).

Fig. 3. Formula Wizard

There are three sections here: the work zone (fig. 4, zone 1), the KPI tree (fig. 4. zone 2), the preview zone (fig. 4. zone 3).

Fig. 4. Zones of the Formula Wizard

In the preview zone, you see the formula created from the data introduced in the work zone.

The work zone has two display modes: Formulas (the default mode) and Weight in %.

Formulas display mode

This mode is for building the formula. To do so, drag the required KPI from the KPI tree into the work zone. To add an operation, use the buttons at the bottom of the work zone. In the Formula Wizard, you can only use arithmetic operations (+, -, *, /), brackets and numbers.

To add a number to the formula, click Number. A new row will be added to the work pane where you need to enter a number.

The KPIs and operations are added line by line in the order of being added into the work zone (fig. 5).

Fig. 5. Formula Wizard. Formulas tab. Work zone.

To delete a KPI from the formula, click

on the left-hand side of the KPI's name.

To change an operation, click

and select the required operation from the drop-down list.

The formula created in this mode will look like this:

Value("KPI")₁<arithmetic operation> ... < arithmetic operation >Value("KPI")_n

Weight in % display mode

In this mode, you can specify the weight of the KPI. The weight determines how much the selected KPI influences the final value of the edited KPI. The weight is measured in %.

To specify the weight of a KPI, drag the KPI from the KPI tree into the work zone. The default weight of a KPI is 100%. To change it, left-click on the number in the Weight, % column and introduce the required value (fig. 6).

Fig. 6.Formula Wizard. Weight in %.

In this tab, the created formula will look like this:

Value("KPI")₁*KPI’s weight (in percentage points)₁+...+ Value("KPI")_n+ KPI’s weight (in percentage points)_n

Attention!

When you are working with the Formula Wizard, each formula is created in a separate tab. If you have created formulas in two different modes, then the system will use the formula from the active mode.

After introducing all the required values, click OK. The created formula will be shown in the Formula field on the Values tab of a KPI page. The created formula can be modified.

Attention!

The KPI values will be calculated from the formula only after the values used in the formula are entered in the system for the next time.