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.

[umasaral]

Hi 

try this whether its fine.

SELECT
(COUNT(CASE WHEN EVENT_NAME = 'TT Submitted' AND appointmentid IS NULL THEN 1 END))
/ NULLIF(COUNT(case_id), 0) * 100 AS percentage
FROM THIS_PROCESS;

[vp_nbn]

Hi, unfortunately I get an error: Oops! Something went wrong The function "=" was called with the invalid argument types LIST_TEXT,TEXT

FYI, I tried using appointmentid without any quotes, as well as with single and double quotes. I always get the above error.

[umasaral]

Hi 

use the following script:
SELECT
(COUNT(case_id)
FILTER (WHERE EVENT_NAME MATCHES ('TT Submitted') AND ATTRIBUTE_NAME IS NULL))
/ COUNT(case_id)
* 100
FROM THIS_PROCESS
Replace ATTRIBUTE_NAME with the name of the event-level attribute you're checking for NULL values. This script calculates the proportion of cases meeting both criteria.

[vp_nbn]

Hello @umasaral ,

thanks for responding. There is probably a mistake in what I do...

I replaced the attribute_name with 'appointmentid', which is the name of the respective attribute. The script looks like this:

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

The result is 0, which doesn't sound right. 

Any ideas what is happening here and how to "fix" it? Thanks.

Kind regards,

Vladimir

[vp_nbn]

Hello, there is probably a mistake somewhere in what I do...