Skip to main content

Celonis Product Documentation

KPI - Saved Formulas
Description

Saved formulas are reusable PQL statements which can be defined in the frontend. With KPI a saved formula can be called. The result is the same as if the saved formula would have been directly placed where KPI is called.

Syntax

Saved formulas can have parameters. The same parameter can be used multiple times in the saved formula. The syntax to define a parameter is as follows:

 {p<index>}
  • index: number of parameter. Begins with one and has to be consecutive, otherwise an error will show up.

The syntax to call a saved formula is as follows:

 KPI ( formula_name (, argument )* )
  • formula_name: Case sensitive name of the formula as string constant

  • argument: column | constant | operator | filter/logical expression | VARIABLE( value ) The parameter in the KPI is replaced by the corresponding argument. If the parameter represents a column, constant, operator or filter/logical expression then the corresponding column, constant, operator or filter/logical expression can be passed as an argument directly. Otherwise VARIABLE( value ) has to be used, where value are arbitrary characters. For example if the parameter represents a list, that should be passed as one single argument or an operator name.

Operator precedence

When a saved formula is called, the saved formula is evaluated first. This means that implicit parenthesis around the saved formula statement are assumed.

VARIABLE argument

If a KPI parameter should represent a list (which can then be used inside an IN, for example), the VARIABLE keyword has to be used to define the content of the parameter. Since the value inside VARIABLE can consist of arbitrary characters, the closing parenthesis ")" and each backslash "\" have to be escaped with a backslash.

Default Saved Formulas

A set of predefined saved formulas is provided. Here is shown how they are implemented:

  • Process variants: SHORTENED ( VARIANT ( activity_column ) )

  • Number of process variants: COUNT ( DISTINCT SHORTENED ( VARIANT ( activity_column ) ) )

  • Ratio: AVG ( CASE WHEN {p1} THEN 1 ELSE 0 END )

  • Filtered count: COUNT ( CASE WHEN {p1} THEN 1 ELSE NULL END )

Examples

[1]

Simple example in which a saved formula without a parameter is called. The saved formula returns a string constant.

Query

KPI "simple formula"

         'Simple Result'
        

Column1

         KPI ( "simple formula" )
        

Input

Output

Result

Column1 : string

'Simple Result'

[2]

In this example a saved formula with two parameters is called.

Query

KPI "add formula"

         {p1} + {p2}
        

Column1

         KPI ( "add formula" , 27 , 15 )
        

Input

Output

Result

Column1

42

[3]

Here a saved formula with one variable parameter in IN operator is called. The parameter uses the optional keyword VARIABLE to specify that the input list is only one parameter.

Query

KPI "saved formula"

         CASE WHEN "Table1"."Column1" IN ({p1}) THEN "Table1"."Column1" ELSE 0 END
        

Column1

         KPI ( "saved formula" , VARIABLE(1, 3) )
        

Input

Output

Table1

Column1 : int

1

2

3

4

Result

Column1

1

0

3

0

[4]

Example of passing a list using the VARIABLE parameter. The list is used inside an IN operator.

Note how some characters inside the VARIABLE are escaped: The closing parenthesis inside the first list item needs to be escaped with a backslash.

The single quote character inside the first list item needs to be escaped with a backslash in general because it is used inside a string constant as described here. Since that backslash is used inside VARIABLE, it has to be escaped again, which leads to two required backslashes to escape a single quote character inside the passed string constant.

A literal backslash inside a string constant has to be escaped with a backslash in general as described here. Since each backslash used inside a VARIABLE needs to be escaped again, a total of four backslashes are required to represent one literal backslash inside a string constant passed via the VARIABLE parameter:

Query

KPI "saved formula"

         CASE WHEN "Table1"."Column1" IN ({p1}) THEN 1 ELSE 0 END
        

Column1

         KPI ( "saved formula" , VARIABLE('Test \) \\' as\\\\1', 'Activity 1') )
        

Input

Output

Table1

Column1 : string

'Test ) ' as\1'

'{v ' " ][ }'

'Activity 1'

'-`=1-/'

Result

Column1 : int

1

0

1

0

[5]

Even tough the parameters have to be consecutively numbered, they don't have to appear in that order in the saved formula. This is shown in the following example.

Query

KPI "print name formula"

         {p2} || ', ' || {p1}
        

Column1

         KPI ( "print name formula" , 'Max' , 'Mustermann' )
        

Input

Output

Result

Column1 : string

'Mustermann, Max'

[6]

Here a saved formula is defined which uses the same parameter twice.

Query

KPI "square formula"

         {p1} * {p1}
        

Column1

         KPI ( "square formula" , 3 )
        

Input

Output

Result

Column1 : int

9

[7]

Here a saved formula containing an addition is defined. Due to implicit parenthesis around the saved formula, the addition is evaluated before the multiplication.

Query

KPI "addition"

         2+5
        

Column1

         KPI ( "addition" ) * 10
        

Input

Output

Result

Column1 : int

70