Beginners questions on PQL statements

Technical Implementation Codes & Statements
#1

Hi everyone,

I am completely new to Process Mining, Celonis and PQL. After spending a few days building an example analysis I have two questions that came up:

  1. I have seen screenshots of Celonis and other tools where you can choose a “Start Activity” and “End Activity” timestamp column. The tool then calculates the duration of the activity. Why can I not select a “End Activity” column in Celonis? For example in Process Explorer, Celonis calculates the connection between two activites as throughput time “Start of A” to “Start of B”, whereas I would like to see “End of A” to “Start of B”.

  2. Now the more complex question. I am struggling with a few rather simple calcuations. These are my tables:

image
image1008×156 4.1 KB

With KPI “Duration” = SUM(MINUTES_BETWEEN(“EVENTLOG_csv”.“START”, “EVENTLOG_csv”.“ENDE”))

So far so good. Next step I would like to aggregate the durations for the diffrent activites on case level (works with same KPI as above) and calculate two more KPIs.

image
image1011×135 3.47 KB

I cannot get the desired results for the KPIs. They should be:

a) time per piece = Duration / Quantity --> Results: 3,333 and 4,000

Obviously, this doesn’t work: (aggregation together with dimension):
time per piece = KPI(“ACTIVITY_DURATION”)/“CASES”.“QUANTITY”

Then for the comparison to target value I would like to calculate:
b) target-actual = time per piece - TARGET_TIME --> Results: 0,333 and 1,000

And lastly, I want to use the case level result of “Target-actual” in several ways, for example:
c) AVG for work center (Result: 0,6665)
image

d) or maybe use it as input for a histogram to display the deviation of actual values compared to target value

What is the best way to tackle these questions?

I know those are probably very basic questions. I have spent some time searching through this community before asking. But right now it feels like I need to see the solution to my specific problem in order to hopefully understand the logic behind PQL.

Thanks for helping!
Patrick

#2

Hi Patrick,

Welcome to the wonderful world of process mining :smiley:

To address your questions:

  1. start and end activities:
    This cannot be done in the analysis but has to be set in the datamodel.
  2. The more complex question:
    a) I guess this should work by removing teh start and ende columns in your ‘activities’ example. Good thing to know is that Celonis ‘groups’ records by the unique combinations of the dimension values. Because start & ende are included, this results in 3 rows. If you remove/hide these 2 dimensions, there should be the 2 rows you expect. You KPI duration is already a SUM so that should work as desired.
    b) Where is target time stored? Is this per case? Or is this a global thing which you can store in a variable?
    c) and d) should follow if a) is solved, so let’s focus on a) first :slight_smile:

Does my answer make sense and help you out?

Regards, Joos

#3

Hi Joos,

thanks for the reply!

  1. I know it should be in the datamodel, but where? Celonis only offers me to select CASE, ACTIVITY, TIMESTAMP and optional SORTING… no second timestamp…

  2. The target time can be unique for every combination of CASE and WORKCENTER. In order to avoid redundance, I created another table in data model. Looks like this now:

image
image694×268 6.19 KB

Took me a while, but now I can the correct results with these formulas:

DURATION = SUM(MINUTES_BETWEEN(“EVENTLOG_csv”.“START”, “EVENTLOG_csv”.“ENDE”))

TIME_PER_PIECE = KPI(“DURATION”) / AVG(PU_AVG(DOMAIN_TABLE(“CASES_csv”.“CASE”), “CASES_csv”.“QUANTITY”))

DEVIATION = KPI(“TIME_PER_PIECE”) -AVG(PU_AVG(DOMAIN_TABLE(“TARGETS_csv”.“ARBEITSPLATZ”, “TARGETS_csv”.“CASE”), “TARGETS_csv”.“TARGET_PROCESS”))

image
image728×121 3.88 KB

Am I on the right track so far? Now how do I “make use” of the results in “DEVIATION” for further calculations, e.g. average on work center (should be 0,666)?

Best wishes
Patrick

#4

Hi,

I found the solution by myself. The problem was in my data model. Before it looked like this:

CASES <-- (1:n)-- ACTIVITIES --(n:1)–> TARGET TIMES

Therefore I was not able to pull QUANTITY from the CASES-Table. I switched the model to:

ACTIVITIES --(n:1)–> TARGET TIMES – (n:1)–> CASES

And after a few changes to the KPI’s I reached the desired solution and was also able to create a histogram! Guess it took me a while to understand how PU-functions work :wink:

Thanks for helping, @joosbuijs!

1 Like