Skip to main content
Question

Touchless Sales Order Item count

  • July 29, 2020
  • 6 replies
  • 13 views
.
Level 2

Hi Team,

I am trying to calculate the touchless order where have defined in dimension

1)COUNT_TABLE(VBAP)

in KPI section have defined SUM(CASE

WHEN (PU_COUNT(VBAP,"_CEL_O2C_ACTIVITIES".USER_TYPE,"_CEL_O2C_ACTIVITIES".USER_TYPE IN (<%= AutoTypes %>)

AND _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>))

= PU_COUNT(VBAP,"_CEL_O2C_ACTIVITIES".USER_TYPE, _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>)))

THEN 1.0

ELSE 0.0

where AutomationActivities and AutoTypes defined in varibale

AutoTypes has only A which is manual so ideally i want count of records which are manually done and will be calculating touchless based upon the below logic

total number of SO item-(KPI logic- count of manual orders)

But am not able to get the count , could you please check and help me on the same.

6 replies

V
Level 0
Hi, you can try to use following formula COUNT_TABLE("VBAP") - ((COUNT(DISTINCT CASE WHEN (PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE","_CEL_O2C_ACTIVITIES"."USER_TYPE" IN (<%= AutoTypes %>) AND "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoActivities %>)) = PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoActivities %>))) THEN "_CEL_O2C_ACTIVITIES"."_CASE_KEY" ELSE NULL END)) - (COUNT(DISTINCT CASE WHEN PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE","_CEL_O2C_ACTIVITIES"."USER_TYPE" IN (<%= AutoTypes %>)) > 0 THEN "_CEL_O2C_ACTIVITIES"."_CASE_KEY" ELSE NULL END))) Please let me know if this helps. Best regards, Viana
.
Level 2
  • .14600841
  • Author
  • Level 2
  • August 7, 2020

https://sjc3.discourse-cdn.com/business6/user_avatar/community.celonis.com/v.kalversberg/40/713_2.png v.kalversberg:

COUNT_TABLE("VBAP") - ((COUNT(DISTINCT CASE WHEN (PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE","_CEL_O2C_ACTIVITIES"."USER_TYPE" IN (<%= AutoTypes %>) AND "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoActivities %>)) = PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoActivities %>))) THEN "_CEL_O2C_ACTIVITIES"."_CASE_KEY" ELSE NULL END)) - (COUNT(DISTINCT CASE WHEN PU_COUNT("VBAP","_CEL_O2C_ACTIVITIES"."USER_TYPE","_CEL_O2C_ACTIVITIES"."USER_TYPE" IN (<%= AutoTypes %>)) > 0 THEN "_CEL_O2C_ACTIVITIES"."_CASE_KEY" ELSE NULL END)))

Thanks Viana,

I am getting some error in formula.

I did found one way please let me know if this this relevant one and also i do get result but not for all

COUNT_TABLE(VBAP)-COUNT(DISTINCT CASE WHEN _CEL_O2C_ACTIVITIES.USER_TYPE IN (<%= AutoTypes %>) and _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>)

and

_CEL_O2C_ACTIVITIES.CHANGED_FROM <> _CEL_O2C_ACTIVITIES.CHANGED_TO AND

_CEL_O2C_ACTIVITIES.CHANGED_FROM <> AND _CEL_O2C_ACTIVITIES.CHANGED_TO <>

THEN (VBAP.VBELN)

ELSE NULL

END)

V
Level 0

Hi,

you are probably getting an error because I used <%= AutoActivities %> as a variable instead of <%=AutomationActivities%>, can you please check if you are using the correct variables?

In your formula you are counting on the header level as you only count the VBELN - I assume you want to count the items - right? You would need to either use VBAP.VBELN||VBAP.POSNR or _CEL_O2C_ACTIVITIES."_CASE_KEY"

What do you want to calculate using the statement

_CEL_O2C_ACTIVITIES.CHANGED_FROM <> _CEL_O2C_ACTIVITIES.CHANGED_TO AND

_CEL_O2C_ACTIVITIES.CHANGED_FROM <> AND _CEL_O2C_ACTIVITIES.CHANGED_TO <>

? The cases that have changes?

Best regards,

Viana

.
Level 2
  • .14600841
  • Author
  • Level 2
  • August 12, 2020

Thanks

You are correct because of variable i was getting erorr,

But that formula is not giving me an output.

Please find the formula which have build to create the touchless orders , so ideally i need all the counts which are changed by manual user(A) for the activities defined.

So far i get correct output, please let me know if its correct or am i missing anything;

COUNT_TABLE(VBAP)-

COUNT(DISTINCT CASE WHEN _CEL_O2C_ACTIVITIES.USER_TYPE IN (<%= AutoTypes %>) and _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>)

AND _CEL_O2C_ACTIVITIES.CHANGED_FROM <> _CEL_O2C_ACTIVITIES.CHANGED_TO

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM <> AND _CEL_O2C_ACTIVITIES.CHANGED_TO =

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM = AND _CEL_O2C_ACTIVITIES.CHANGED_TO <>

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM = _CEL_O2C_ACTIVITIES.CHANGED_TO

THEN (VBAP.VBELN||VBAP.POSNR)

ELSE NULL

END)

.
Level 2
  • .14600841
  • Author
  • Level 2
  • August 24, 2020

Hi Viana,

I am still strugling with touchless KPI ,I get result for some and for some its not working,

if we treat header changes as item changes as well (if there is at least 1 header change, then it means that all SO items have been changed).

But if we take into account sales order items only, then the number of changes is lower.

Could you please help on the same.

Formula currently used

COUNT_TABLE(VBAP)-

COUNT( CASE WHEN _CEL_O2C_ACTIVITIES.USER_TYPE IN (A, ') and _CEL_O2C_ACTIVITIES.ACTIVITY_EN IN (<%=AutomationActivities%>)

AND _CEL_O2C_ACTIVITIES.CHANGED_FROM <> _CEL_O2C_ACTIVITIES.CHANGED_TO

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM <> AND _CEL_O2C_ACTIVITIES.CHANGED_TO =

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM = AND _CEL_O2C_ACTIVITIES.CHANGED_TO <>

OR _CEL_O2C_ACTIVITIES.CHANGED_FROM = _CEL_O2C_ACTIVITIES.CHANGED_TO

THEN (VBAP.VBELN)

ELSE NULL

END)

A
Level 1

Hey,

Maybe this is a little overcomplicating the problem?

When I calculate No Touch orders, I would try use PU_COUNTS based on your list and compare them with the actual process steps in the same list, which are automated:

100 * AVG( CASE WHEN PU_COUNT("VBAP", "_CEL_O2C_ACTIVITIES"."USER_TYPE","_CEL_O2C_ACTIVITIES"."USER_TYPE" = 'Automated' AND "T_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoTypes %>)) = PU_COUNT("VBAP", "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN", "T_ACTIVITIES"."ACTIVITY_EN" IN (<%= AutoTypes %>)) THEN 1 ELSE 0 END )

I think this should give you the percentage of fully Automated Processes, that are in the allowed list.

Best regards

Community Code of ConductAccessibility statement