OLAP table touchless activities

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.

2020-12-22_11-09-13761117 28.7 KB

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

Page 1 / 1

Hi Jonas,

Im not that familiar, but what type of table is the VBAP table? Is it your case or activity table? If its 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.

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:

image802200 5.36 KB
In line 2 and 4 in column Touchless I would expect 1.
Many thanks for your support.
Best regards,
Jonas

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.

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 its 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

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?

image111994 4.24 KB

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)

image738174 18.2 KB

Many thanks for your support,

Best regards,

Jonas

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

Hi @d.becher ,
yes now it works.

.
Thanks a lot for your efforts and for the solution!
Have a nice week!
Best regards,
Jonas

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN