OLAP table touchless activities

[Jonas_S]

Dear all,

I would like to create an OLAP table with the activity as dimension. And I would like the following KPI as a column:
If all activities with the name of the activity in the dimension ran automatically for a sales order item, then count 1, otherwise count 0.
For example:
So if the activity “Change Price” occurs 3 times for an order item, and all price changes are delivered automatically, then count 1.
But if at least 1 price change was manual, count 0.
The following code does not work unfortunately. I suspect that is because the dimension is on activity level, and the calculation on item level.

SUM(CASE
WHEN
PU_COUNT(“VBAP”, “_CEL_O2C_ACTIVITIES”.“USER_TYPE”, “_CEL_O2C_ACTIVITIES”.“USER_TYPE” NOT IN (<%=AutoTypes%>)) = 0
AND
PU_COUNT(“VBAP”, “_CEL_O2C_ACTIVITIES”.“USER_TYPE”, “_CEL_O2C_ACTIVITIES”.“USER_TYPE” IN (<%=AutoTypes%>)) > 0
THEN 1
ELSE NULL
END)

But is there any way to calculate this?
Many thanks in advance!
Best regards,
Jonas

[joosbuijs]

Hi Jonas,

I’m not that familiar, but what type of table is the VBAP table? Is it your case or activity table? If it’s case then that might be the source of the error.

Could you also share the error, or the incorrect result you get? That would help.

[Jonas_S]

Hi,
many thanks for your reply.
Yes exactly, VBAP table is our case table.
I get not an error, but an incorrect result as you can see in the example case below:

In line 2 and 4 in column “Touchless” I would expect 1.

Many thanks for your support.
Best regards,
Jonas

[joosbuijs]

Hi Jonas,

Did you try dissecting your PQL formula? For instance, did you try to make a KPI for each of the PU_COUNT statements, and then show the result for each case (e.g. case ID is dimension)?
Usually breaking it down and checking the individual more complex building blocks before combining them helps in understanding where and what goes different than expected.

[d.becher]

Hi @Jonas_S

Your condition returns false for all cases because of the first PU_COUNT. Here you count the number of events for each case which have a manual user type. If that number is not 0, the condition is not fulfilled, and you return NULL. So you will get NULL for every case which has at least one activity with an AutoType user, independent of which activity this is.

As @joosbuijs already suggested, you want to calculate the KPI based on Activities and Cases, rater than on Case level only.

COUNT DISTINCT ( 
CASE WHEN 
PU_COUNT( DOMAIN_TABLE("Activities"."Case", "Activities"."Activity"), "Activities"."Activity")
=
PU_COUNT( DOMAIN_TABLE("Activities"."Case", "Activities"."Activity"), "Activities"."Activity", "Activities"."UserType" IN (<%=AutoTypes%>) )
THEN "Activities"."Case" ELSE NULL END )

Here you compute for every <Case,Activity> pair how often this pair occurs - the first time without any conditions, the second time you only count the activity when it’s an automatic one. If the two numbers are equal, then all occurrences of this activity in this case must be automatic. In that case, we can return the Case ID, otherwise NULL. By doing this, we can simply do a COUNT DISTINCT around the CASE WHEN, and use the Activity dimension that you already have in your OLAP table to group by the Activity, while counting on a Case level.

Hope this works for you!

Cheers
David

[Jonas_S]

Hi @d.becher ,

thanks a lot for your really detailed answer. When I run your code I still get an error message. Do you have any idea what the reason could be?

COUNT DISTINCT(
CASE WHEN
PU_COUNT(DOMAIN_TABLE("_CEL_O2C_ACTIVITIES"."_CASE_KEY", “_CEL_O2C_ACTIVITIES”.“ACTIVITY_DE”), “_CEL_O2C_ACTIVITIES”.“ACTIVITY_DE”)

PU_COUNT(DOMAIN_TABLE("_CEL_O2C_ACTIVITIES"."_CASE_KEY", “_CEL_O2C_ACTIVITIES”.“ACTIVITY_DE”), “_CEL_O2C_ACTIVITIES”.“ACTIVITY_DE”, “_CEL_O2C_ACTIVITIES”.“USER_TYPE” IN (<%=AutoTypes%>))
THEN “_CEL_O2C_ACTIVITIES”."_CASE_KEY" ELSE NULL END)

Many thanks for your support,
Best regards,
Jonas

[d.becher]

Hi Jonas,

sorry, my bad! The DISTINCT needs to go inside the parenthesis:

COUNT ( DISTINCT
CASE WHEN 
PU_COUNT( DOMAIN_TABLE("Activities"."Case", "Activities"."Activity"), "Activities"."Activity")
=
PU_COUNT( DOMAIN_TABLE("Activities"."Case", "Activities"."Activity"), "Activities"."Activity", "Activities"."UserType" IN (<%=AutoTypes%>) )
THEN "Activities"."Case" ELSE NULL END )

Can you please check if that works now?

Thanks!

David

[Jonas_S]

Hi @d.becher ,

yes now it works. .
Thanks a lot for your efforts and for the solution!

Have a nice week!

Best regards,
Jonas