Issue with proportion of #change activity/#cases ratio

Hi ,

I am encountering wrong results with change ratio formula below.

SUM(KPI(“Priority - Change_Indicator”))/ COUNT_TABLE("_CEL_ITSM_CASES")

where KPI(“Priority - Change_Indicator”) is

CASE WHEN MATCH_ACTIVITIES(NODE_ANY[ ‘Change Priority Medium to Low’, ‘Change Priority Low to Critical’, ‘Change Priority Low to High’, ‘Change Priority Low to Medium’] ) = 1 THEN 1.0 ELSE 0.0 END

Formatting used is Percentage With Decimals

This KPI was working fine in our dashboards from long time and started giving weird values since few weeks with same formula.

The results of numerator and denominator are ok when executed separately . The only problem is when I combine with both / operator.

It also works fine when I change the numerator from MATCH_ACTIVITIES to CASE WHEN “_CEL_ITSM_ACTIVITIES”.“ACTIVITY” LIKE ‘%Change%’ but I don’t want to use this because there are tens of formula which need to be modified.

I could not identify the exact issue so far to address the customer question-
why do same formula give wrong results now?

You inputs and guidance is really appreciable.

Thank you in advance.

Regards,
Kavya

Hi Kavya,

I assume the error occurs because activity and case level are interfering with each other. The formula CASE WHEN MATCH_ACTIVITIES(NODE_ANY[ ‘Change Priority Medium to Low’, ‘Change Priority Low to Critical’, ‘Change Priority Low to High’, ‘Change Priority Low to Medium’] ) = 1 THEN 1.0 ELSE 0.0 END is on activity level whereas the other kpis are on case level.
Can you please try using following formula:

COUNT(DISTINCT CASE WHEN MATCH_ACTIVITIES(NODE_ANY[ ‘Change Priority Medium to Low’, ‘Change Priority Low to Critical’, ‘Change Priority Low to High’, ‘Change Priority Low to Medium’] ) = 1 THEN “_CEL_ITSM_CASES”.CASE_ID ELSE NULL END)

Best regards,
Viana