SIGNAL query issue - including event-level attribute

[vp_nbn]

SAP Signavio Process Intelligence

In a dashboard within SAP Signavio Process Intelligence, I want to calculate a proportion/ percentage of cases which include a specific activity ("TT Submitted") AND have one event-level attribute with the value NULL.

For a simpler version of the script (percentage of cases which include a specific activity) I use this script:

SELECT
(COUNT(case_id)
FILTER (WHERE (EVENT_NAME MATCHES ('TT Submitted'))))
/ COUNT(case_id)
* 100
FROM THIS_PROCESS

I would really appreciate help from the forum. Thanks.

Kind regards,

Vladimir

[JessicaK]

Hello, thank you for the question! In this case, please create a support case using SAP for Me (log in with S-User ID and password). Kind regards, Jessica

[umasaral]

Hi 

check this ,I’ve made sure that the CASE statement is used correctly and that potential type mismatches are avoided:
SELECT
(COUNT(CASE WHEN EVENT_NAME = 'TT Submitted' AND appointmentid IS NULL THEN 1 END) * 1.0)
/ NULLIF(COUNT(case_id), 0) * 100 AS percentage
FROM THIS_PROCESS;

Multiplication by 1.0: Ensures that the division operation results in a floating-point calculation rather than integer division.
NULLIF: Prevents division by zero by returning NULL if COUNT(case_id) is zero.

Additionaly try this.
Check Data Types: Ensure that EVENT_NAME and appointmentid are indeed the correct data types and match your conditions.
Debug with Simplified Query: Run simpler queries to validate that each part of your CASE condition works as expected:

SELECT
COUNT(CASE WHEN EVENT_NAME = 'TT Submitted' AND appointmentid IS NULL THEN 1 END) AS count_null_appointments,
COUNT(case_id) AS total_cases
FROM THIS_PROCESS;

This will help you verify if there are issues with the conditions or counts.