Saved formulas are a useful tool to save PQL statements. The saved formulas may be also be used by other analysts in the analysis.
Manage saved formulas
Create saved formulas
All PQL statements supported in the Celonis analyses can be stored in a saved formula. The only exception are variables. Variables are not supported in saved formulas! To apply variables within saved formulas, read the next section. |
Saved formulas can be used in all formula inputs in the analysis.
KPI("<Name of the saved formula>" [, '<Parameter Input>']) |
Fast access to the saved formulas is provided with:
The data chooser
The library in the visual editor
The pql reference
Parameters allow you to abstract a part of the saved formula's statement and the parameter will provide a type specific input in the visual editor. The end user re-using the saved formula in a component has to set the input for the parameter you defined either in code or in the provided input block of the visual editor. Parameters are set on the saved formulas by putting the parameter's placeholder, e.g. {p1} into the formula. The following parameter types are available:
Parameter type text
Saved formula
Applied on visual editor: Input B will replace {p1} on the formula statement.
Applied on code editor: B will replace {p1} on the formula statement.
KPI("Ratio of Users", 'B') |
Parameter type integer
Saved formula
Applied on visual editor: Input 100 will replace {p1} on the formula statement.
Applied on code editor: 100 will replace {p1} on the formula statement.
KPI("Ratio of Users", 100) |
Parameter type decimal
Saved formula
Applied on visual editor: Input 1.0 will replace {p1} on the formula statement.
Applied on code editor: 1.0 will replace {p1} on the formula statement.
KPI("Ratio of Users", 1.0) |
Parameter type condition
Saved formula
Applied on visual editor: Usertype equals B will replace {p1} on the formula statement.
Orignial state:
With input values:
Applied on code editor: "_CEL_P2P_ACTIVITIES"."USER_TYPE" = 'B' will replace {p1} on the formula statement.
KPI("Ratio of Users", "_CEL_P2P_ACTIVITIES"."USER_TYPE" = 'B') |
Parameter type column
Saved formula
(optionally a default value for the selected column can be set)
Applied on visual editor: The selected column via the available dropdown will replace {p1} on the formula statement.
Initial state:
Selected columns:
Applied on code editor: "_CEL_P2P_ACTIVITIES"."USER_TYPE" will replace {p1} on the formula statement.
KPI("Ratio of Users", "_CEL_P2P_ACTIVITIES"."USER_TYPE") |
Parameter type activity name
Saved formula
Applied on visual editor: The selected activity via the available dropdown will replace {p1} on the formula statement.
Initial state:
Selected activity:
Applied on code editor: 'Change Price' will replace {p1} on the formula statement.
KPI("Custom activity ratio", 'Change Price') |
Parameter type table
Saved formula
(optionally a default value for the selected table can be set)
Applied on visual editor: The selected table via the available dropdown will replace {p1} on the formula statement.
Initial state:
Selected table:
Applied on code editor: "_CEL_P2P_ACTIVITIES" will replace {p1} on the formula statement.
KPI("Custom Count Items", "_CEL_P2P_ACTIVITIES") |
Parameter type date and time
Saved formula
Applied on visual editor: The selected timestamp will replace {p1} on the formula statement.
Applied on code editor: 2017-12-6 8:56:45:00 will replace {p1} on the formula statement.
KPI("Datediff against eventlog", {d '2017-12-6 8:56:45:00'}) |
Combine parameters and variables to adjust formulas from the analyses frontend
You can use a combination of dropdown buttons and the saved formulas to allow users with only view rights to adjust what KPI they are shown in a chart or table. The following example will showcase how you can set up the Rework Ratio to be dependent on the activities selected by the user: