Group similar cases by Clustering

 Posted on 2021-10-02  |  2 minutes  |  397 words  |  Kazuhiko Takata

Last week I posted Convert Quantitative value to Categorical one by Quantile Function to create categorical dimension. Today I will create categorical dimension via different way, clustering. Clustering is one of the unsupervised learning method, to automatically group cases by their attributes. Celonis PQL has clustering funciton KMEANS, so you are ready to use clustering. Today I will use O2C process and would like to group customers by (1) their lead time from Invoice send to Clear Invoice and (2) net value. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Convert Quantitative value to Categorical one by Quantile Function

 Posted on 2021-09-25  |  3 minutes  |  429 words  |  Kazuhiko Takata

Last week I posted Integrate Button Dropdown Entries to one Formula and integrated multiple dimensions. At that time I used string column that enable you to categorize each case, that is called categorical variable. In contrast quantitative variable such as order quantity, net value is not normally possible to use for dimension. If you would like to use these column as dimension, you need to convert its value to categorical value. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Use BIND function to enable multiple DOMAIN_TABLE

 Posted on 2021-09-11  |  3 minutes  |  520 words  |  Kazuhiko Takata

Variant Explorer is the major Celonis Analytical view to find process pattern by GUI operation. Of course I used it many times and enjoyed it at first, and found that I had a lot of effort to filter on and off to observe process. Today I would like to create collective list of variant to see major process KPI. Also I would like to show you BIND function that is difficult to understand but quite convenient if you know it. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function  Variant Explorer 

Convert count unit of KPI by COUNT DISTINCT

 Posted on 2021-09-04  |  3 minutes  |  430 words  |  Kazuhiko Takata

Last week I was asked to convert count unit of some KPI (that returns 1 or 0) from delivery item to delivery document (convert if all items in the document are 1 then document KPI is 1, else 0). In this case delivery document and item columns are stored at Activity table like this post’s third topic. I already used DOMAIN_TABLE to group activity record by delivery item then calculate KPI. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Count rows of Tables in various way

 Posted on 2021-08-28  |  2 minutes  |  425 words  |  Kazuhiko Takata

Counting rows of tables is frequently used so we are not aware about how to do it. But sometimes I got stuck to do it so I would like to deep dive this topic today. For this demo case I use P2P data model and three tables, header EKKO (key columns are MANDT/EBELN), item EKPO (MANDT/EBELN/EBELP) and activity. Also I used sum of item Net value. Below screen is the result of this demo. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Categorize and Name Activity

 Posted on 2021-07-24  |  3 minutes  |  471 words  |  Kazuhiko Takata

In the previous post Transform Source System Tables to Minimize Data Model Tables, I recommended to convert some kind of source system tables to Activity. Today I focus on Activity and would like to share my way how to categorize and name Activity. First point is to split Activity name to two parts, more general part and detail part. For example, in SAP ECC or S4HANA Order to Cash process, general Activity name is Create Sales Order when data committed in VA01 transaction. [Read More]
Data Model  Data Integration  Transformation  PQL  Process Analytics  Studio  Analysis  Pull Up Function  Process Explorer 

Maintain Saved Formulas effectively

 Posted on 2021-07-03  |  3 minutes  |  485 words  |  Kazuhiko Takata

In the last post Handle NULL efficiently in Aggregation Function, I used saved formulas to split long and complex PQL to reusable components. Today I would like to share my best practice to use saved formulas. For example I would like to ananlyze throughput time between arbitrary two activities. As below screenshot I created three dropdown buttons, switching time unit (sec, min, hour, day etc.) and two activities (from / to). [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Handle NULL efficiently in Aggregation Function

 Posted on 2021-06-26  |  3 minutes  |  552 words  |  Kazuhiko Takata

I looked at many PQLs that can be simplified if they know about NULL handling well. Today I would like to tell how to handle NULL efficiently in Aggregation Functions (COUNT,SUM,AVG etc.). Today I would like to use O2C process to explain my case. I determined KPI Send Invoice within a day after Ship Goods, because sales company will Ship Goods then should Send Invoice immediately. Same as previous posts, first I would like to create OLAP table to look into the cases. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Use Pull up function as Subquery

 Posted on 2021-05-29  |  2 minutes  |  420 words  |  Kazuhiko Takata

In the last post Determine First Time Right by Pull up function, I broke down PQL of FTR step by step. Today I will explain more complex KPI in similar way. By the way you may know Subquery that enables to pass result of SQL to parts of another SQL. PQL can also do similar things by Pull up function. In this post, for example I would like to calculate Average rework count after Delivery in Order to Cash process, and definition of rework is same as previous post. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function 

Determine First Time Right by Pull up function

 Posted on 2021-05-22  |  2 minutes  |  337 words  |  Kazuhiko Takata

As a first example of Pull up function, I will show you First Time Right (FTR) that means process without rework activities (link). To determine whether each case is FTR or not, first I should determine rework activities, then judge if each case has rework activities or not. In this example I determined that rework activity name is started from Change. PQL to find such a string pattern is Change %, using wildcard % after Change. [Read More]
PQL  Process Analytics  Studio  Analysis  Pull Up Function