Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

FAQ - Process Query Language

Description

In this section, you can find the answers to frequently asked questions concerning PQL.

General

Which characters are allowed inside table or column names?

All information about supported table and column names can be found here.

An error with "Could not open swap file: " occurs. What to do?

Try to do a "Force Complete Reload" in the data model.

Data type conversion

Detailed information about data type conversion can be found here.

How to cast from INT to FLOAT?

Converting an INT to a FLOAT can be done via *1.0.

How to cast from FLOAT to INT?

Converting a FLOAT to an INT can be done via the FLOOR or the ROUND operator.

How to cast from STRING to INT?

Converting a STRING to an INT can be done via the TO_INT operator.

Null handling

How to change the result of a KPI from NULL to 0?

Use the COALESCE operator.

How to catch an empty variable in a filter statement?

To avoid errors for empty variables, the variable syntax allows conditional statements. The following example will only apply the filter statement, if the variable is not empty:

<% if(NEW_VARIABLE != "") { %> FILTER "table"."column" LIKE '%<%=NEW_VARIABLE%>%'; <% } %>

KPI calculation

Why is KPI("Number of process variants") different from COUNT(DISTINCT VARIANT("_CEL_ACTIVITIES"."ACTIVITY" ))?

The predefined saved formula KPI("Number of process variants") uses the SHORTENED operator with the default max_cycle_length of 2.

Why do the results of KPI values in OLAP tables changes depend on other KPIs?

In general, Celonis performs implicit joins when a query accesses several tables. The joins are executed according to the foreign key relationships defined in the data model. Therefore, the calculation of individual KPIs depends on the table for which the join is executed. More information about the join functionality in Celonis can be found here.

How to find out if the average value of a company is above or below the overall average value?

Use the GLOBAL operator to calculate the overall average value and compare it with the average value of a company:

CASE WHEN AVG ( "Companies"."Value" ) ) > GLOBAL ( AVG ( "Companies"."Value" ) ) THEN 'larger' ELSE 'smaller' END

Throughput Time calculation

How to calculate the maximum number of days between any two activities in the process for each case?

Use the PU_MAX operator in combination with the SOURCE / TARGET operator.

PU_MAX("_CEL_CASES",SECONDS_BETWEEN(TARGET("_CEL_ACTIVITIES"."EVENTTIME"),SOURCE("_CEL_ACTIVITIES"."EVENTTIME")))

Further information about PU functions and SOURCE/TARGET can be found on our cheat sheets here.

Why differs the throughput time in the Process Explorer and a Single KPI component?

There are two possible reasons for this:

  • Rounding issues: The throughput time in the Process Explorer is calculated with the SOURCE / TARGET operator by mapping the timestamps via REMAP_TIMESTAMP to SECONDS. Afterwards, the result is converted to MINUTES/HOURS/ DAYS in the frontend. If the throughput time calculation in the Single KPI is done by mapping the timestamps via REMAP_TIMESTAMP to MINUTES, this can cause differences in the two results.

  • Different ways of calculation: As mentioned in the previous point, the throughput time in the Process Explorer is calculated by using the SOURCE / TARGET operator. If the throughput time calculation in the Single KPI is done with another operator, this might cause differences in the results.

Some detailed examples on the calculation of throughput times can be found here.

Why don't the throughput time numbers in the Variant Explorer add up?

The variant explorer can show the throughput time in two different ways:

  • The median throughput time of all cases belonging to a variant, right next to its case coverage

  • The throughput time between each activity by switching the edge KPI to throughput time

You might expect that adding all the throughput times between the activities will lead to the same number as shown right next to the case coverage. This is not always the case. A possible deviation is caused by adding median results. The throughput time is calculated by taking the median. In general, median is more robust against outliers but there is no guarantee that the sum of medians of subgroups returns the same result as the median over the whole group. Some detailed examples on the calculation of throughput times can be found here.

Conformance calculation

How to use the results of the Conformance Checker in other parts of the analysis, for example in an OLAP table?

Store the conformance query to a variable. Then you can calculate the

  • Overall conformance: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN 1 ELSE 0 END)

  • Conforming Throughput time: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN CALC_THROUGHPUT(CASE_START TO CASE_END, REMAP_TIMESTAMPS("_CEL_ACTIVITIES"."EVENTTIME", HOURS)) / 24 ELSE NULL END)

  • Conforming steps per case: AVG(CASE WHEN PU_SUM("_CEL_CASES", ABS(<%=conformance%>)) = 0 THEN PU_COUNT("_CEL_CASES", "_CEL_ACTIVITIES"."ACTIVITY") ELSE NULL END)