Solved

Hi all,I would like to build a chart which shows the number of open activities at a given day.In our data activities are a combination of a task (e.g. modelling or testing), and a (lifecycle) status (e.g. new, in progress, done, rejected). I would like to plot per day the number of open tasks/activities of a certain type.I currently have the activity timestamp aggregated to day as DIMENSION, and the following KPI:MOVING_COUNT_DISTINCT(CASE WHENMATCH_ACTIVITIES(NODE[Testing - In progress] , EXCLUDING[Testing - Done, Testing - Rejected] ) = 1THEN L33_V_MBD_INZICHTEN_STATUS.FEATUREAPGIDELSE NULLEND,-999,0)In (my?) theory this should use the activities up until the day, and then determine whether the case contains an open testing task or not. However, my current result is incorrect:image1223594 18.2 KBWhich trick am I missing?BTW: were on CPM4.6Happy holidays!

We have a ServiceNow analysis and the business would like to know how long tickets are spending in a series of states. We are trying to analyse if we are putting calls on hold for too long. We are looking to do two things. For any set of tickets give me the total time this ticket has spent in a particular activity. So the event time of this activity until the next activity. The ticket may be in this state more than once through it's process journey. There are more than one of these states. Report on any tickets that have exceeded a specific threshold of time in one of these states. So the flow might go Ticket Created - Time 1Ticket On Hold - Time 2Ticket Active - Time 3Ticket On Hold - Time 4Ticket Active - Time 5Ticket Resolved - Time 6 I'm looking for the total time between Time 2 & Time 3 added to the time between Time 4 and Time 5. I cannot see a way to do this. Any ideas greatly appreciated.  

Hi there,I am getting this strange behaviour and was wondering if somebody can explain this?I want to create an activity for when NETPR in CDPOS changed to 0 but when I look for 0s the table is empty. Query 1: My first entry happens to be a case that I am looking for.    SELECT DISTINCT FNAME,VALUE_NEW from CDPOS    WHERE FNAME = 'NETPR'   ORDER BY VALUE_NEW   LIMIT 1    FNAME  VALUE_NEW   NETPR  0.00 Query 2: But I get an empty table when I use these conditions.     SELECT DISTINCT FNAME,VALUE_NEW    FROM CDPOS    WHERE FNAME = 'NETPR' AND VALUE_NEW = '0.00'    FNAME  VALUE_NEW   - Next I wanted to see if my VALUE_NEW = '0.00' might be wrong somehow.    Why do I get different smallest values '0.000' and '0.00' from query 3 and query 4 respectively? Query 3   SELECT DISTINCT VALUE_NEW from CDPOS   ORDER BY VALUE_NEW     VALUE_NEW    0.000    1.000 Query 4   SELECT DISTINCT FNAME, VALUE_NEW   FROM CDPOS   WHERE FNAME = 'NETPR'  ORD

Hello All, Is there any way to get the sum of the Throughput time other than what is there. For example want to group by with some activities, the throughput only available for the avg, median and mean. If we can get the sum of the same. 

Like the Title says, I have 18 Queued Executions of a Studio Skill that have been pending for 30m Here is a picture of the flow: Does anyone know how I can cancel / restart?It was running fine when I had just a couple of columns being written to the table, I've since added a number of columns and changed the Table Name  Thank you in advance!

Hey Celonis world. I am trying to follow the Assistant Guided tour, but sometime it jumps and restart. is there some docs where we can do the exercises without following the assistant? Thks in advance

Hi, I want to add a column to an OLAP table that shows whether all orders from a given supplier exceed the value of 100 or not. So the final table should show a supplier name, the number of orders, the net worth of orders and either: The net worth value sum if its above 100, else 0An 'x' when the sum of orders exceeds 100 Also, it would be great to see how many single orders exceeded the value of 100. I was trying around for a while with PULL functions and CASE WHEN but I can't seem to make it work with the restriction of '> 100'. For example the query below gives me an error message stating that CASE is not compatible with FLOAT and INT. SUM(CASE WHEN "EKPO"."NETWR_CONVERTED" > 100THEN "EKPO"."NETWR_CONVERTED"ELSE 0END) Thank you very much in advance. Best regardsAram

The logic is as follows:if a category repeats (like C1), then:for each category, calculate the date difference (DATEDIFF) between "closed at" date of a row with "closed" status and the "opened at" date of the rows with an "open" status. The objective is to have an extra column to the right, with these values:Difference (in days)18 (DATEDIFF(dd,17.09.2021,30.08.2021)16 (DATEDIFF(dd,15.09.2021,30.08.2021)N/A (because its status is closed)N/A (because the category doesn't repeat)N/A (because the category doesn't repeat) Suggestions?

Hello all, I'm enrolling Celonis Summer School and almost in the end of the analysis I noticed that I made mistake at the beggining while uploading "_APX_WDCRP_CASES.csv. Basically in "event collection" »» "File uploads" »» "Decimal Operator", I didn't change "." to "," and so the program is not recognizing the numbers as decimals.  As I'm having my first steps with Celonis, can anyone let me know if this is possible to fix without need of making the analysis from scratch?? Thank you for your kind.Guilherme Luz

Hi all, I'm currently enrolling Celonis Summer School and I have no experience on PQL neither SQL languages, so I please ask your help. I am trying to write something like a "SUM IF" expression. Basically, when column "_APX_WDCRP_CASES_csv"."ORDER_DATE_MET" = 0, I want to sum "_APX_WDCRP_CASES_csv"."ORDERED_QUANTITY" values.  I already tried to mix CASE WHEN function with SUM but I was not succeeded. I also searched for other functions related with aggregation but could not find one that applied. Many thanks!Guilherme Luz

As we know, Process Mining has the tremendous advantage of providing an objective and realistic view on the processes, as opposed to the traditional BPM analysis, which is very dependent on human judgement and prone to bias.However, building a process model from an event log is not a straightforward task. It requires an algorithm, and algorithms are based on assumptions have to somehow deal with things like noise. Conformance checking can also be done in different ways. Therefore, to better understand my results, I would be very interested in learning the algorithms used by Celonis. Thanks!

Example: CASE WHEN YEAR("X"."y") >= 2019 THEN FILTER ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ELSE FILTER ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01 END Neither CASE WHEN nor IF worked for me. I'm trying in a Component Filter, though a solution on a Sheet level could also solve my problem.

Hello all,I'm looking for a way to search for certain activities without having to show all the variations and then read over them with my eye. An example from P2P process:I search "Delete Purchase Order Item". But I don't know from which variant it becomes visible in the explorer. How can I search/find something like that? 

Based on the example table below:I am getting the values in 'Column_2' by using PU_COUNTHow would I go by converting each value in 'Column_2' to a percentage amount?

Hello everyone, I wonder that when I create a skill with Manual Sensor, can i schedule trigger of this skill ? For example I want to trigger this skill once in a month automatically . If I can't is there any differenw way to do that Thank you 

Hi!! I'm interested in filtering an activity out of an analysis. When I try to filter out the activity from the attribute selection, the activity still shows in the Process Explorer. Is there a way to do a crop selection type filter in the load script?

Hi!I have a doubt about a calculation related to the duration between activities. Normally the CALC THROUGHPUT formula is used to calculate durations between 2 activities.However, I need to calculate the duration between more than 2 activities in a single KPI (e.g. the duration between activities A and B to activity C, or from A and B to C and D), but I can't get the formula. I already have tried the following formula to calculare the duration between activities 'A' and 'C' to 'B' AVG (CASEWHEN PROCESS EQUALS 'A' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['A'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp"))  WHEN PROCESS EQUALS 'C' TO ANY TO 'B' THEN CALC_THROUGHPUT(FIRST_OCCURRENCE['C'] TO LAST_OCCURRENCE['B'],  REMAP_TIMESTAMPS("activity_table"."timestamp", HOURS)) ELSE 0 END )  Can you help me? Thank you very much!!

Hi, is there a way to add weekdays to a date?E.g. Goods Issue date is on a Fridayand we need to add 2 weekdays (Monday through Friday are weekdays)to the Goods Issue Date, so that we end at a Delivery Day of Tuesday (Friday + 2 weekdays)ADD_DAYS would give us Sunday as output, and ADD_WORKDAYS only works with a workday calendar (needs TFACS table which we do not have)REMAP_TIMESTAMP even with the ADD_DAYS function would bring us to Monday as the next weekday.

Hi, I am trying to filter the data using CASE WHEN statement in the component filter section but it is not working.Scenario: when DATE_1 is not NULL then show data only till yesterday using DATE_1 (DATE_1 < TODAY()) else show data till yesterday using DATE_2 (DATE_2 < TODAY()). This works file if I execute single statement like FILTER DATE_1 < TODAY() but it is not working when I combine into CASE WHEN like below: FILTER CASE WHEN ISNULL(DATE_1)=0 THEN DATE_1 < TODAY() ELSE DATE_2 < TODAY(); Any help is really appreciated. Note: We are using Celonis on-prem 4.5

HI, I have embarked on learning the usage of MERGE_EVENTLOG in Celonis Snap environment. I cannot set two tables as Activity Table at the same time, making it unable to go further investigation.How could you set several tables as Activity Table (and Case Table as well) at the same time?

Hello All, I have a csv file (attached a screenshot of the same) which has the Case ID (column A), Activity (Column N) and the Timestamp (column O) in the correct format. There are also other columns in the file that I will be using for my analysis. The error (screenshot attached) says that there are more than 100 empty cells within the Acitivity, Timestamp and CaseID columns but I have double-checked and can confirm that, that is not the case. Has anyone experienced this before? What could be the issue over here?  This is an urgent matter so any quick responses would be much appreciated! Thanks!@1460045682​ ,@joos.buijs​ , @hugo.victo11​ 

Hi Celopeers :) We have a specific use case, based on JIRA data. For a case a limited set of activities (~7, JIRA stories) need to be performed. For each activity (/story) we know when it was ready for pickup, in progress, and done (or rejected). If we would like to calculate the total time the activity/story spent in the status 'in progress' I was thinking of something like this: SUM( CASE WHEN "TABLE_STATUS"."STATUS" = 'In progress'   THEN HOURS_BETWEEN("TABLE_STATUS"."STATUSDATETIME",ACTIVITY_LEAD("TABLE_STATUS"."STATUSDATETIME")) / 24 --to get fractional days   ELSE 0.0 END) Where the dimension could be the different activity/story names for instance. However, the activity_lead often is a very different activity (/JIRA story), hence the calculation fails.I also cannot use a process pattern matching using first and last occurrence, as an activity can be 'in progres' multiple times and I would need to

In my analysis filter I need a range over three month exactly.With this formular I get the last day of the preceding month:ADD_DAYS(ROUND_MONTH(TODAY()), -1) Today: 07.06.2021 Result: 31.05.2021This is the secound part of the BETWEEN order.Now I'm searching for the first formular in the BETWEEN ordersImilar to VBA: DateSerial(YEAR(Date()),MONTH(Date())-3,1)Is there a chance? 

So, I tried to solve it with a pull-up function. PU_COUNT("VBAK", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN ('Credit Approval Release') )   But the results do not seem correct to me:I do not think this offers a distinct valuewhen putting the KPI in an OLAP against the customer, I get duplicates for the customer. Another option that I could think of, made use of match_activity. However, this is then still at line item level.