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

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:

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 …

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 :slight_smile:

You cannot write =<, it must be <= .

Now it should really work.

Cheers
David

Hi david,

Is it work ! :slight_smile: But the count is wrong:

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 you’re trying to achieve.
Most likely, you get a different value than expected due the following reason (I assume that you’re 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 don’t 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 don’t 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) doesn’t 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:

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 don’t 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:
image

Laura

I think you’re 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. That’s 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 :slight_smile:

Cheers
David