Dear Celomates,I would like to add the rework rate as a KPI without using PQL.

Hi Omar,

For purposes of this feedback I would assume the following case table and activity table (note that your case might vary and then you would just need to adjust to your process accordingly):

• Case table: VBAP
• Activity table: _CEL_O2C_ACTIVITIES
• Activity name column: "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN"

I would assume you have used the "Case When" statement when deriving the "Rework" column of your OLAP table (e.g. something like " case when count ("_CEL_O2C_ACTIVITIES"."ACTIVITY_EN") = count (DISTINCT "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN")

then 'No' else 'Yes' end"). The problem with calculating the ratio by directly utilizing this "Yes" or "No" entries (or by utilizing the count of activities and distinct activities in this way), is that aggregation inside of an aggregation is not allowed inside of the PQL (irrespective if you use a Visual Editor or Code Editor). Your "rework rate" (yes/no) is not an inherent data model field thus you would need to again use count on count which is not allowed. In addition your table has many rows for each case, whereas you want to arrive at a single KPI which will unite all of these cases and show you the overall rework rate.

Thus, the easiest way to arrive at the rework rate would be the following similar formulas:

• AVG((CASE WHEN PU_COUNT("VBAP", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN") > PU_COUNT_DISTINCT("VBAP", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN") THEN 1.0 ELSE 0.0 END))

This follows the similar logic as your approach with count of distinct activities. The major difference is the utilization of the PU function instead of the standard aggregations.

Hi, i’m also trying to calculate the rework rate in a single KPI, i have also tried modifying the above PQL  but its still showing error what to do,

AVG((CASE WHEN PU_COUNT("_CEL_CSV_ACTIVITIES"."_CASE_KEY", "_CEL_CSV_ACTIVITIES"."ACTIVITY_EN") > PU_COUNT_DISTINCT("_CEL_CSV_ACTIVITIES"."_CASE_KEY","_CEL_CSV_ACTIVITIES"."ACTIVITY_EN") THEN 1 ELSE 0 END))

ERROR MESSAGE

A PQL error is preventing this query from executing.

Syntax error near [.] after reading [TABLE(AVG((CASE WHEN PU_COUNT("_CEL_CSV_ACTIVITIES"] at line 1. Please refer to PQL documentation for available syntax.