Count activity list by automation

[santiagolc]

Good afternoon,

I want to add a signal table in Signavio Process Intelligence that shows the name of the activities, the number of cases covered by each one and finally the automation of each activity.

I tried with the following code but the result is not ideal. I tried several ways but in none of them I could get what I need.

SELECT
event_name AS "Actividades"
,count(case_id) 
,SUM ((SELECT COUNT (EVENT_NAME) FILTER (WHERE ("Event Created By User Type" IN ('System', 'Service', 'Batch Job'))))) / SUM ((SELECT COUNT (EVENT_NAME))) * 100 AS "Automation rate"
FROM THIS_PROCESS<br>

This it's the result:

Thank you very much, best regards.

[TeawithThi · Accepted solution]

Dear Santiago,

the code for automation rate for one activity across all cases (or the selection) is

SELECT

(COUNT(event_name) FILTER (

WHERE "USER_TYPE" IN ('B'))

/ COUNT(event_name)) * 100

FROM FLATTEN(THIS_PROCESS )

WHERE event_name IN ('Create Invoice')

If you want to see the occurrences of all activities and their respective automation rate, it could go like this,

where the BLUE BAR is the # of occurrences (the wider the more often) and RED LINE is the rate of automation

SELECT

count("event_name") as "Event occurrences",

COUNT(event_name) FILTER (

WHERE "USER_TYPE" IN ('B'))

/ COUNT(distinct case_id) as "Automation Rate"

, "event_name"

FROM flatten(THIS_PROCESS)

order by 1 desc

limit 10

[santiagolc · Accepted solution]

Thanks for your help, this code is what I was looking for!