Data Ingestion & Infrastructure

The median function in the Celonis IBC appears to not work correctly when there is aneven count of items in a data set. MEDIAN() appears to take the top middle number and display it instead of correctly calculating the average between the two middle numbers to get the true median of the data. Here is an example picture showing the difference between Celonis and Excel. Is there a fix for this or a way to calculate the true Median of a data set in Celonis when there is an even number of items?Median_Calculation1137897 77.9 KB

Hi , i am trying to use sql cursor in transfornation job but every time i execute it it shows " error near or at @". I am using @ to declare variable for eg @sample int

Hello, While testing the upload of several data sets in Celonis, I have encountered the following issue: we dont always have one row the per event. What is the most efficient (and standard) way to write generic scripts to transform an event log that doesnt have one row per event? I encounter the following cases: One row of headers, 2 rows per event: Headers: Event ID / Timestamp Event 1 / Time started DD.MM.YYEvent 1 / Time ended DD.MM.YYEvent 2 / Time started DD.MM.YYEvent 2 / Time ended DD.MM.YY Or even the following: Headers: Event ID / Attribute / Value Event 1 / Event type / CreateEvent 1 / Timestamp / DD.MM.YYEvent 2 / Event type / ClosedEvent 2 / Timestamp / DD.MM.YY I have been able to do some things in Excel but as I want to load bigger files (to work on real cases, not only test samples) this becomes an issue. Is there anything we can leverage?

How can i rank users based on the Date appeared in the table.This table is not a activity table unfortunately.The table has users and multiple date assigned to them and i want to know the Date they first appeared in that table. I can use OLAP table and just use a MIN Function but i also want to show it in a Column Chart. Basically that Column Chart will have a week as dimension and no.of users in KPI.I should be able to differentiate New Users vs Existing for a Particular week. Any Advice Please ?

Dear team,We have an indicator related vendors who has been paid with both, MM and FI invoices and we calculate this as a ratio, so far so good.The problem is when we want to provide the list of those vendors for further review, We have the dimension Vendor and one calculated column that states whether the vendor has FI and MM invoices or either FI or MM which we understand is not subject to review and its listed as OKas the vendor has both FI and MM is calculated as an aggregation. (See formula below) I can not implement a filter just to show only those that are FI + MM , in the screenshot below i would like to exclude those that are OKDo you have any suggestion?All the best ,image1418575 31 KBCASEWHENSUM( CASE WHEN BKPF.AWTYP != RMRP AND BSEG.BSCHL = 31 THEN 1.0 ELSE 0.0 END ) > 0 ANDSUM( CASE WHEN BKPF.AWTYP = RMRP AND BSEG.BSCHL = 31 THEN 1.0 ELSE 0.0 END ) > 0THEN FI + MMELSE OKEND

Hello everyone,I have a question about quick display of dates in a certain form.There are two date-time fields, A and B. I need to calculate the difference between them and to display it in user-friendly format HH:MM:SS. For example, A = 12:30:05, B=14:20:00, then I want to have 1:49:55.Using the celonis function DATEDIFF(ss) or SECONDS_BETWEEN I obtain a big number like 6595.an I get this time difference in the desired format HH:MM:SS in some natural way, without using tiring formulas like SECONDS_BETWEEN(A,B)/3600 || : || SECONDS_BETWEEN(A,B)/60 || : || SECONDS_BETWEEN(A,B)%60 and so on?Thank you,BestPolina

Is there a way to delete already uploaded files on Event Collection>File Uploads.

Hello,Im trying to use 2 olap tables (one in front of the other) and a button dropdown with two variables and I want that one olap table come to the front layer when I select one of the variables in the dropdown button and when I select the other variable in the dropdown button the other table comes to the front layer again. Is this possible?Thanks in advance

Hello,I would like to filter a field after comparing it to a variable.The variable value is only a part of the field value, that is why I would like to use the "LIKE % % function.I tried to define % and % as wrapping characters for the variableI also tried to write % and % manually in the component filter formula.( FILTER TABLE.FIELD LIKE % <%=VAR%> %; )None of these worked. Is there a solution for this?Thank you very much for your help,Best RegardsRobin

Hi All, i am trying the load the data in celonis system. After successful data is loaded, it shows some error like The timestamp and sorting columns dont define a distinct order of the activity. Eventhough the sorting is properly defined, still the error exists. Could you please help me out? Thanks in advance Celonis Error851179 14.6 KB

Hi,Id like to create a component filter, that only displays cases in the process explorer that dont have any activity with a manual user type. The result is, that we only see the process, where every activity is automated.I tried:FILTER ISNULL(PU_COUNT(VBAP, activity.user_type, activity.user_type IN (<%=AutoTypes%>))) = 0;but it didnt work. I think, pu_count must be wrongHas anyone an idea?Best regards,Carina

Hi,According to the data engineering track, the Celonis transformation uses Vertica SQL. I searched online and find Vertica SQL website: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Other/SQLOverview.htm?tocpath=SQL%20Reference%20Manual|_____1I follow the Vertica SQL examples and syntax, but for some examples, I cannot execute them in Celonis transformation statement editor part. Why this happens? Where can I find the exact documentation/manuals for Celonis transformation?Thanks.

Hello Dear All,I am doing an analyze for a supplier companys inventory. The data-set is like this in the image below.Screenshot 2020-01-07 at 14.08.041495829 62.5 KBThe data set is for the last 4 years, and I just want to calculate the daily cases for each product, for determining the amount of Sale order per day for each product.I would appreciate if you could advice me a way to calculate avg daily sale order for each product id.Thank you so much and have a nice working days

Hi All,Currently I have an OLAP table with, amongst others, Sales Order value, Invoice value and a check if these two are equal. For the first two columns (SO value and Invoice value) I just changed the formatting of the column in order to apply 2 decimal rounding. Hence, its showing 3319.94 for both SO and Invoice.However, as per standard SAP these field actually have 15 positions. The results in a problem when Im applying some filtering. Currently I have a filter in place to show only cases where the delta is bigger than 0. Although for the above case the delta is 0, it still showed up in my table. Reason being that actually the SO has a value of 3319.94000000000545 and the Invoice has a value of something like 3319.939999999999929.Question now is how to make use of the rounded decimals values instead of the actual values in a filter or formula. Stated in Excel terms, I want to filter on cases where:abs(round(SO value, 2) - round(Invoice value,2)) > 0It seems like the Round functi

Hello Dear All,I am doing an analyze for a supplier companys inventory. The data-set is like this in the image below.Screenshot 2020-01-07 at 14.08.041495829 62.5 KBWhat I want to do is to determine the valuable products according to frequency and price rank. So, for this purpose I firstly aggregate the all products with price rank and created new column represent the frequency counted.Screenshot 2020-01-07 at 14.56.44435820 18.6 KBAnd frequency is calculated with this KPI: COUNT_TABLE(PM_Last.csv_CASES)And now I just want to apply a manual RFM algorithm to determine most valuable products. So, I need to use the values of this two column and create a monetary value in another column. For this, I just need to multiply the 2 column, such as the formulation that I tried COUNT_TABLE(PM_Last.csv_CASES) * PM_Last.csv.BuyPriceRankBut, I receive this error message : Screenshot 2020-01-07 at 14.12.45786133 9.82 KBI think, what I want to do is not complex but I couldnt find a way to use aggrega

Hi, I would like to create an analysis for three of our depts. together.The department is one of the characteristics of activities and the whole process is not only happens in those 3 depts of interest.If I use filter ACTIVITY_TABLE.DEPT IN (1,2,3) Celonis cuts all the activities that were performed in other departments. I am rather interested in all the cases that were ever touched by my 3 Departments no matter if some of the activities were performed somewhere else - the process should be shown completely.Thank you!

Dear all,Currently we are doing some analysis on our O2C process. We are making use of a data model in which the delivery document line item is the central case_id in the case table. Attached to this case table are, amongst others, the earlier steps in the process (i.e. Sales Order data) as well as later steps in the process (i.e. Invoice data). A graphical representation for the analysis of 1 SO Line item (which contains 2 cases here) looks as follows:image909261 6.49 KBIn the above example, the specific SO_line 100 consists of two deliveries and two invoices (delivery line item:invoice = 1:1 relationship)In Celonis weve created the following overview for this SO Line item:image1168107 9.59 KBINVOICE Q is defined as PU_SUM(VBAP, _CEL_O2C_CASES.INVOICE_QTY)Besides, we have a date picker component to filter on billing date. However, so far it doesnt work as we hoped it would. The idea is that if we enter 01/01/2019 - 31/12/2019 as date range, only the first billing document (Dec 20th) i

Hi,I am currently working on getting the Manual Delivery Rate?What do i need to modify on this code that is currently getting the On-Time Delivery Rate?100.0*AVG(CASEWHEN ISNULL(<%=DUE_DATE%>) = 1 THEN NULL No ConfirmationWHEN ISNULL( PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>') ) = 1 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) <= 0 THEN NULL --'No GI and Future Due Date' WHEN ISNULL( PU_LAST("VBAP",ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."ACTIVITY_<%=language%>"='<%=record_goods_issue_activity%>') ) = 1 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) > 0 AND DAYS_BETWEEN(<%=DUE_DATE%>,TODAY()) <= <%=TOO_LATE_BUFFER%> - COALESCE(<%=ROUTE%>*1.0,0.0) THEN NULL -- 'No GI

Hello,I am trying to create a dropdown filter, where users can select activities and get all cases which pass through these activities. I tried 2 versions:Using CASE WHEN and returning TABLE.ACTIVITY when it fits a certain pattern (with e.g. TABLE.ACTIVITY LIKE String%Example%). The problem here is that if I select any activity in the filter, my report is filtered just for the date of that activity and not the whole process chain. I.e. if I have a case that spans over 4 months, this filtering will only return that month when the activity happened.Using CASE WHEN with PROCESS EQUALS and listing 1 activity in each WHEN option. It looks something like this:CASEWHEN PROCESS EQUALS Activity A THEN Activity AWHEN PROCESS EQUALS Activity B THEN Activity BELSE NULLENDHowever this filter doesnt work with other filters and presents only 1 option. If I filter for some specific case which includes 5 of listed activities, this filter only allows me to select the first listed option. So filtering e.

Dear Community, is there a way to customize the CPM4 Help Page that you get, when you click on your user name and then Help? I mean this one: grafik897371 20.4 KB The reason is that we do not want users to contact the celonis help desk directly but see a company-internal e-mail adress and phone number that they can use. Thank you

Dears, Im trying to calculate Payment terms mismatch. For MM related Invoices,Payment terms should be equal to PO payment terms.For this motivation, Im trying to obtain the field EKKO.ZTERM to compare it against BSEG.ZTERM. But Im block because of no not have a common parent in the schema message.I try different approaches using pullout functions but Im stuck . Can you please advise? below the relations available and the error messageMy formula should look like something like this:CASE WHEN (EKKO.ZTERM)<>(BSEG.ZTERM) THEN NOK ELSE OK ENDI read that somehow this can be achieved through the DOMAIN_TABLE but to be honest I could not manage to get it. Below Im including a screenshot of the data model mapping.image1589721 136 KB

Hello,I try to count the number of sales order items based on a filter on headers.I manage to do that:SUM(CASEWHEN VBAK.BSARK NOT IN (ZWEB, DFUE, ZMYP, ZMWS, ZTEL, ZPBI)AND VBAK.ERNAM NOT IN (BZTLK, BTS16)AND VBAK.VBTYP = CTHEN 1ELSE 0END)But it only counts sales orders and not sales order items.Note that I manage to do it with a Pivot Table (since we can choose on KPI to count VBAP or VBAK) but I also want to have percentages on the same OLAP table.Thank you in advanceAntoine

Hi All,Currently I have a dropdown button with manual entries. All of these entries set two variables, of which one concerns the name of the selected entry.Preferred value to set for the name:Exclude Invoice Value of Zero: YesHowever, so far I didnt find a way to get this Yes in bold. Regular ctrl + B as well as and (double asterisk) operators dont work. Is there any way to get this done?Thank you!Jeroen

Hi,I have a problem that was addressed in another topic however, I have tried the solution and it did not work.Basically i would like to sum up the euro value of invoices that have been completed within 2 working days. We dont have the calendar table in our data model so using the working day formula does not work and therefor I have tried the below work around that has been suggested. However, am getting an error.Can you please let me know where my error is?SUM( CASE WHEN DAYS_BETWEEN( REMAP_TIMESTAMP (ROUND_DAY(BKPF.BLDAT, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00 ),REMAP_TIMESTAMP (ROUND_DAY(BSEG.AUGCP, MONDAY 08:00-17:00 TUESDAY 08:00-17:00 WEDNESDAY 08:00-17:00 THURSDAY 08:00-17:00 FRIDAY 08:00-17:00)) <=2.0 THEN BSEG.EUROELSE NULLEND)Thanks for your help!Anna

Hi Team, Like list of supported connectors available with celonis. Where I can find list of supported and unsupported Database with celonis ? Thanks.