Cancellation Quote of Activities


#1

Is there a way to calculate for each Activity (in an OLAP table) the cancellation quote, i.e.:

#Cases for which it was the last activity/ #Cases which flow through that activity in total

I already tried it with SOURCE and TARGET, but I couldn’t work it out…


#2

Hi Dirk,

the solution should be:

COUNT(DISTINCT CASE WHEN “ACTIVITY_NAME” = PU_LAST(CASES, “ACTIVITY_NAME”) THEN “CASE_ID” ELSE NULL END)
/
COUNT(DISTINCT “CASE_ID”

and “ACTIVITY_NAME” as dimension


#3

Hello Linda,

could you please check the formula as I got error message that table “ACTIVITY_NAME” is not found.
best regards
Christian


#5

Hi Cristian,

when using columns in a PQL formula, you need to include the table from which you take the column e.g. when using “ACTIVITY_EN” write “CASES”.“ACTIVITY_EN”.
The formula should be:

COUNT(DISTINCT CASE WHEN “_CEL_ACTIVITIES”.“ACTIVITY_EN” = PU_LAST("_CEL_CASES", “_CEL_ACTIVITIES”.“ACTIVITY_EN”)
THEN “_CEL_CASES”."_CASE_KEY" ELSE NULL END) * 1.0
/ COUNT(DISTINCT “_CEL_ACTIVITIES”."_CASE_KEY" ) * 1.0

Please replace “_CEL_ACTIVITIES” by the name of your activity table and “_CEL_CASES” by the name of your case table.

Best regards,
Kerstin


#6

Hello Kerstin,
thanks a lot, now it’s working but the results in the OLAP row could not be used as filter to find the cases in the detailled analysis. I assume that is because of the complex calculation that only the result is shown but use as filter is possible.
best regards
Christian