Question
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)))
avg1353839 98.7 KB
Is there any way to calculate average based on cases in PE ?
Thank you.
+2Hi,
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,
DavidThank 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.
avg2850792 61.6 KB
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.
+2Hi,
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 youre on the IBC.
Best,
DavidEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.