Count Case When Cycle Time equal to

Hello,
I would like to count how many items have a crossing time under 1 day from activity1 and activity2.
To calculate the crossing time, I used this formulated that I recorded in saved formulas:
CALC_THROUGHPUT(FIRST_OCCURRENCE[<%=From%>] TO FIRST_OCCURRENCE[<%=To%>],
REMAP_TIMESTAMPS("_CEL_P2P_ACTIVITIES".EVENTTIME, <%=unit%>))
Then I tried to write the following formula to do the count but it gives me an error:
COUNT(CASE WHEN KPI(TEMPO DI ATTRAVERSAMENTO) =< 1 THEN 1 ELSE 0 END)
Is there another solution?
Thanks

Page 1 / 1

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 !

Lets say your Activity_1= A and Activity_2= B

you can code like as follows in order to find how many days passed btw. Activity A and Activity B.

CALC_THROUGHPUT ( LAST_OCCURRENCE E 'A' ] TO FIRST_OCCURRENCE E 'B' ] , REMAP_TIMESTAMPS ( "Activities"."Timestamp" , DAYS ) )

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN