Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

KPI - Saved Formulas

Applies to: CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 CELONIS 4.7

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 (, parameter )* )
  • formula_name: Name of the formula as string constant

  • parameter: VARIABLE( value )

    • value: Parameter which should be inserted in the saved formula. The keyword VARIABLE is optional.

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.

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: SUM ( CASE WHEN {p1} THEN 1.0 ELSE 0.0 END ) / COUNT ( ISNULL ( CASE WHEN {p1} THEN 1.0 ELSE 0.0 END ) )

  • Filtered count: COUNT ( CASE WHEN {p1} THEN 1.0 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. For the first parameter the optional keyword VARIABLE is used. Besides that there is no difference between the first and the second parameter.

Query

KPI "ADD FORMULA"

{p1} + {p2}

Column1

KPI ( "add formula" , VARIABLE(27) , 15 )

Input

Output

Result

Column1 : INT

42

[3] Even though 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'

[4] 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

[5] 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