Hi Huseyin,

In the activity table, you can add an automation index. Value should be 0 for automated and 1 for manual.

Then you use PU_SUM to summarize the column on case level.

If this is > 0, you mark the case manual.

PQL for the KPI:

CASE WHEN

  PU_SUM (

    "_CASE_TABLE",

    CASE  

      -- condition for what you consider manual activity

      WHEN "_CEL_MERGED_ACTIVITIES"."USER_TYPE" = 'A'

      THEN 1

      -- condition for what you consider automated activity

      WHEN "_CEL_MERGED_ACTIVITIES"."USER_TYPE" <> 'A'

      THEN 0

    ELSE NULL

    END

  ) = 0

  -- when 0 it means all activities for the case are automated

  THEN 'Automated'

  ELSE 'Manual'

END

Or calculating the automation rate directly:

AVG (

  CASE WHEN

  PU_SUM (

    "VBAK",

    CASE  

      -- what you consider manual activitiy

      WHEN "_CEL_MERGED_ACTIVITIES"."USER_TYPE" = 'A'

      THEN 1

      -- what you consider automated activity

      WHEN "_CEL_MERGED_ACTIVITIES"."USER_TYPE" <> 'A'

      THEN 0

    ELSE NULL

    END

  ) = 0

  -- when 0 it means all activities for the case are automated

  THEN 1 -- fully automated cases

  ELSE 0

  END

)

Thanks for help, I've tried to implement these formulas but unfortunately it did not work for me.

I have created this formula:

sum (

CASE WHEN

  PU_SUM (

    DOMAIN_TABLE("A"."B"),

    CASE  

      WHEN "A"."C" in_like ('%X%', '%Y%')

      THEN 1

          WHEN "A"."C" not in_like ('%X%', '%Y%')

      THEN 0

    ELSE NULL

    END

  ) = 0

  THEN 1--'Automated'

  ELSE 0--'Manual'

  END

)

Result is: 3234

and this one:

(count(distinct "A"."B"))

Result is: 7807

I put them in the KPI list; they are working fine separately. However, when I use them in the same formula below; it is not working correctly

sum (

CASE WHEN

  PU_SUM (

    DOMAIN_TABLE("A"."B"),

    CASE  

      WHEN "A"."C" in_like ('%X%', '%Y%')

      THEN 1

          WHEN "A"."C" not in_like ('%X%', '%Y%')

      THEN 0

    ELSE NULL

    END

  ) = 0

  THEN 1--'Automated'

  ELSE 0--'Manual'

  END

)

/

(count(distinct "A"."B"))

Result is 0.425

But it should be 3234/7807=0.414

I really don't understand why it is working differently. Do you have any idea?