Aggregation based on cases on PE

Hi Community.

I am trying to put average of CS(Customer Satisfaction) on PE.
The CS is based on Cases.

I set Custom KPI like below.

avg(“case_tbl”.“cs”)
avg(source(“act_tbl”.“cs”))

They returned average based on activities.

I thought PU functions would work and tried them,
but they return the same value.

avg(PU_MIN(“case_tbl”, “act_tbl”.“cs”))
avg(PU_MIN(“case_tbl”, source(“act_tbl”.“cs”)))

Is there any way to calculate average based on cases in PE ?

Thank you.

Hi,

The query you tried does not work because due to the join with the activity table, the CS value is weighted by the number of activities. You can see this in the “Activity Table” OLAP table in your screenshot.

You need to calculate the Global Average value, independent of the Activity table. If you are on the IBC, you most likely already have the new CONSTANT() option for PU functions. You can then do this as both the activity and the edge KPI:
PU_AVG(CONSTANT(), "case_tbl"."cs")

Hope that helps!
Cheers,
David

Thank you becher.

Sorry. I failed to tell what I wanted to get.
I tried CONSTANT() option and they returned identical values (1.5).

I added Activity C and took the new screen shot.


Only case id [CS001] goes through activity C, edge B->A and edge B->C,
so I hope their average CS would be 1.0.

Thank you.

Hi,

ok now I understood your question.

For the Activity KPI, you can use INDEX_ACTIVITY_TYPE to always only take the first activity of each case into account:
AVG(CASE WHEN INDEX_ACTIVITY_TYPE("Activities"."Activity") = 1 THEN "Cases"."CS" ELSE NULL END)

For the edge KPI, you can use INDEX_ORDER to do a similar thing:

AVG(CASE WHEN INDEX_ORDER(SOURCE("Activities"."Timestamp"), GROUP( "Cases"."CaseId", SOURCE("Activities"."Activity"), TARGET("Activities"."Activity") ) ) = 1 THEN "Cases"."CS" ELSE NULL END)

This should work if you’re on the IBC.

Best,
David

1 Like

Hi, David

Thank you!
That PQL returnd exactly what I expected!

Best,
Takayoshi