Count Case When Cycle Time equal to

Hi Laura, since your KPI returns an integer (number of specified time units), you need to compare it to an integer (or float) as well. So you need to say <= 1 instead of <= '1'. I think this is what causes the error. However, you need to consider also the following: COUNT counts the number of rows in the input that are not NULL. In your CASE WHEN, you either return 1 or 0, however, both count as 1 in the COUNT function. So you need to return NULL instead of 0 in the ELSE statement to not count those. Best David

Hi David, I try with <=1 then also with NULL instead 0 in the ELSA but I have the same error: image.png1042714 25 KB image.png1038721 25.2 KB Best Laura

Hi Laura, are your Activities in the To and From variable surrounded by single quotes? (for example 'Book Invoice') If not, you need to add the single quotes to your formula: CALC_THROUGHPUT(FIRST_OCCURRENCE['<%=From%>'] TO FIRST_OCCURRENCE['<%=To%>'], REMAP_TIMESTAMPS("_CEL_P2P_ACTIVITIES"."EVENTTIME", <%=unit%>)) It should work now. Cheers David

Hi David, Yes there is image.png137598 4.16 KB Laura

Hi Laura, then you need to let me know which Celonis version you are using, then I can check on that version. Cheers David

Hi David, I am using Celonis 4.4 - 4.4.0-RELEASE_18422d5ccd_20180723_1140 Best Laura

Alright, I think I got it now, very hard to see You cannot write =<, it must be <= . Now it should really work. Cheers David

Hi david, Is it work ! But the count is wrong: image.png773668 15.3 KB image.png761665 15.3 KB The result would be :

• 7.938 without NULL values
• 35.237 with NULL values

Best Laura

Hi Laura, the first version counts the number of cases where the thoughput time between the first From and the first To activity is smaller than 1 time unit. You can change the formula depending on what youre trying to achieve. Most likely, you get a different value than expected due the following reason (I assume that youre always using MINUTES as the time unit): REMAP_TIMESTAMPS returns an integer, so only full minutes. Now, if there are for example 1 minute and 40 seconds between 2 activities, because of you are using full minutes, this results in a throughput time of 1 minute, which is what you count. However, you probably dont want to count those. You could fix this by using SECONDS as time unit and compare it to <=60 (seconds). Another reason might be that if you have multiple occurrences of To and From activities, you only count the case based on the first pair, although the second pair might be interesting for you as well. Or you dont want to calulate the KPI based on the case level at all, but more on the activity-pair level? Your second variant (returning 0 instead of NULL in the ELSE) doesnt make sense, it basically only counts all cases which contain at least one From and one To activity. If you want to use 0 instead of NULL here, you need to use SUM instead of COUNT (but the first solution is more efficient) Cheers David

Hi David, No, I an working in day. Below, what the client would like to see: image.png487701 22.9 KB The result is right but do not include NULL - formula with NULL in ELSE (before I had some filter that why I wrote the result was wrong) Best Laura

Hi Laura, I dont understand what you mean with "do not include NULL - formula with NULL in ELSE ", Can you please clarify what is wrong with the current formula? Thanks! David

Hi David, By NULL I mean the - you see on the grafic. By Formula with NULL in ELSE, I mean: COUNT(CASE WHEN (CALC_THROUGHPUT(FIRST_OCCURRENCE[<%=From%>] TO FIRST_OCCURRENCE[<%=To%>], REMAP_TIMESTAMPS("_CEL_P2P_ACTIVITIES".EVENTTIME, <%=unit%>))) <=1 THEN 1 ELSE NULL END) Best Laura

Hi Laura, so if I understand you correctly, you want to remove the NULL value from the pie chart such that you only have numbers for 0days and 1days? Then you need to write a component filter statement which excludes the NULL value, the formula depends on the dimension you are using in the pie chart. David

Yes, sorry for the confusion! In my pie chart, I would like to exclude NULL value ("-" in this case), and add a number component with the total of 0days and 1days. Laura

This heavily depends on the dimension you are using in your pie-chart. But what you basically need to do is write a component filter like this: FILTER <your dimension> IS NOT NULL; Cheers, David

It is not working if I wrote the following filter: Laura

I think youre not using the right query for it. Can you please send all the queries of your pie chart component? Dimension and KPI query?

Dimension: CALC_THROUGHPUT(FIRST_OCCURRENCE[<%=From%>] TO FIRST_OCCURRENCE[<%=To%>], REMAP_TIMESTAMPS("_CEL_P2P_ACTIVITIES".EVENTTIME, <%=unit%>)) KPI: COUNT(EKPO.EBELP)

Okay. Thats fine then, I just forgot that you are using a CPM4.4. In this case, you need to use FILTER ISNULL( CALC_THROUGHPUT(FIRST_OCCURRENCE[<%=From%>] TO FIRST_OCCURRENCE[<%=To%>], REMAP_TIMESTAMPS("_CEL_P2P_ACTIVITIES"."EVENTTIME", <%=unit%>)) )=0; in you component filter Cheers David

Perfect !! It is working image.png529546 23.2 KB Thank You !