Case_key count including & excluding certain activities

Hi Community,

Hope everyone is staying healthy.

I would like to build up our KPI statement but am having trouble making the formulas work as they should.

I would like to count certain activities from the activities tables, but also exclude certain cases if they also pass through for example ‘Changes to Document’ activity.

For example:
COUNT(DISTINCT CASE WHEN (“ACTIVITIES”.“ActivityEN” = ‘Start of FI Non-Compliant Approval’ AND “ACTIVITIES”.“ActivityEN” != ‘Changes to Document’)
THEN “CASES”."_CASE_KEY" ELSE NULL END)

When applying the above formula, i get the same result regardless if I included the the "!= ‘Changes to Document’ or not.

I have also tried using a PU_COUNT formula but receive an error message:
PU_COUNT_DISTINCT (“CASES”.“ACTIVITIES”.“ActivityEN”) IN ‘Start of FI Non-Compliant Approval’
AND PU_COUNT_DISTINCT (“CASES”.“ACTIVITIES”.“ActivityEN”) NOT IN ‘Changes to Document’
THEN “CASES”."_CASE_KEY" ELSE NULL END

Please help.

Cheers,
Anna

Hi All,

I am still trying to work through the above issue.

I have adopted my code as per the below, but it is still not pulling the information correctly.

We have a touch-less flag in the backend which i am utilizing. The touchless flag in the backend, looks at whether or not the activities throughout the AP process were performed by bots. If that is the case, then the invoice is considered touchless. Now i would like to add certain activities, that regardless of who the user is, should also be considered touchless.

COUNT(DISTINCT CASE WHEN “CASES”.“RSNonTouchlessFlag” = 0 OR <%= Activities2 %> THEN “CASES”."_CASE_KEY" ELSE NULL END)

Variable for Activities2 : “ACTIVITIES”.“ActivityEN” = ‘Start of FI PO Exception Approval’
OR “ACTIVITIES”.“ActivityEN” = ‘Start of Down Payment Approval’

Doing this pulls the additional activities that i wanted, but it also pulls in activities not in my list, but that have also been performed not by bots.

Can someone please provide me with suggestion, that might make this work?

All the best,
Anna

Hi Anna,

regarding your original question: You wrote that you want to count activities, but in fact, you’re counting cases based on activities. As probably every case contains an activity that is != 'Changes to Document', this condition does not change your result.
If you want to exclude a case if one of its activities equals ‘Changes to Document’, you need to calculate this information on case-level rather than on the activity level. You can either do this with PU functions, or use, for example, CALC_REWORK:

CALC_REWORK ( "ACTIVITIES"."ActivityEN" = 'Changes to Document' )

This returns the number of ‘Changes to Document’ activities per case, so if it’s > 0, then you want to exclude the case. So you can adapt your CASE WHEN accordingly:

COUNT(DISTINCT CASE 
WHEN CALC_REWORK ( "ACTIVITIES"."ActivityEN" = 'Changes to Document' ) > 0 THEN NULL
WHEN ("ACTIVITIES"."ActivityEN" = 'Start of FI Non-Compliant Approval')
THEN "CASES"."_CASE_KEY" ELSE NULL END)

For the second question, I’m not entirely sure what you mean by “it pulls also other activities in”. How do you check this? In the query, you again count full cases rather than activities, so as soon as one single activity of a case fulfills one of the conditions in your OR, the case will be counted. If you want to count activities rather than cases, you need to return the activity column in the THEN.

Best
David

Hi David,

Thank you for your response & apologies for not being clear. Ultimately, I am looking to amend our touchless KPI. Currently, any invoices processed 100% via BOT are considered touchless. There are certain steps in our process that are performed by a manual user, however the business has decided that its not really a manual task but a triggering of sorts. Therefore, i need to include these cases that go through these specified activities only with the user being a manual user. If at other points in the process a manual user performs an activity, that invoice should not be counted in my overall count.

I thought the code in my 2nd question would fix this, but as you said, as soon as the Or condition is fulfilled then the invoice is counted. But really i only wanted it counted if its a manual user only in that activity and BOT for all other process steps.

I hope this is a bit clearer.

Thanks for your help.
Anna

Hi Anna,

in that case, I would reformulate the question: You don’t want to include cases that have at least one manual activity (based on your definition of a manual Activity).
So you can make a PU function counting the number of activities that are executed by the manual user while they are not excluded. Those are activities that are actual manual activities. If you have at least one of them in your case, you don’t want to count the case, otherwise, you do count it. Here’s the query:

COUNT (
CASE WHEN 
PU_COUNT("Cases", "Activities"."Activity", "Activities"."UserType" = 'manual' and "Activities"."Activity" != 'Changes to Document' ) > 0 
THEN NULL ELSE 1 END
)

In your second post you mentioned the “RSNonTouchlessFlag” column, which is per case, rather than per Activity. I did not yet understand what this does here, and if you need it for your query.

Best
David

Hi David,

Can you please give me a call when you have a moment. +44 (0) 7825 756905 . I believe it will be easier to explain over the phone what my goal is.

Cheers,
Anna

Hello,

I cannot call you unfortunately because I don’t have a company phone.
You can contact me via email, and we can set up a zoom call: d.becher@celonis.com

But what is wrong with the query I proposed? In my opinion this should satisfy your requirements.

Best
David