Machine Performance: plan vs actual time. How to create a Pareto Chart

Hello Celonis Community,

Celonis 4.5

I need to create a pareto chart showing the Delta hours (planned-actual) for each production phase (e.g. setup, production, breakdown,…).
Each phase can have different unique activities, for which I´ve defined dedicated variables :
image

The actual data are derived from the ACTIVITIES table.
image

The Target data are located in another table:

The two tables are not directly linked. The common parent table is the CASE table.


I use the component Column Chart and I add

  • Dimension: KPI(“Category”), so defined
    CASE
    WHEN “ACTIVITIES”.“ACTIVITY_EN” IN (‘A’,‘B’,‘C’)
    THEN ‘Setup’
    WHEN “ACTIVITIES”.“ACTIVITY_EN” IN (‘D’,‘E’,‘F’)
    THEN ‘Production’
    WHEN “ACTIVITIES”.“ACTIVITY_EN” IN (‘G’,‘H’,‘I’)
    THEN ‘Dismantling’
    WHEN “ACTIVITIES”.“ACTIVITY_EN” IN (‘L’,‘M’,‘N’)
    THEN ‘Breakdown’
    ELSE NULL
    END

  • KPI:
    CASE
    WHEN KPI(“Category”) = ‘Setup’
    THEN SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Setup”))-
    SUM(HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”))

    WHEN KPI(“Category”) = ‘Dismantling’
    THEN SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Dismantling”))-
    SUM(HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”))

    WHEN KPI(“Category”) = ‘Production’
    THEN SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Production”))-
    SUM(HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”))

    WHEN KPI(“Category”) = ‘Breakdown’
    THEN SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Breakdown”))-
    SUM(HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”))

    ELSE 0.0
    END

This is the error I get. Do you know how to solve this?

Thank you in advance for your support.
Kind regards,
Francesca

1 Like

Hey Francesca,

This is best solved by adding the dimension line directly to the Activity Table by adding an additional row to the Event Log called Category where you implement the same logic you used in your current Dimension.

This way you can easily sum up the times for each individual category on a case level and subtract them from the planned hours.

Best Regards,

Benedict

Hi Bene,

thanks for the quick answer… However, I´m not sure I follow.
Do you suggest that I modify the structure of the table ACTIVITIES, so that I have the category explicitly available without building up any formula like the one in Dimension?
Basically the dimension formula should be embedded in the Table ACTIVITIES itself as a new column entry.
If so, I would need to ask for the data scientist support, as I´m just an analyst.

Many thanks,
Francesca

Hey Francesca,

The problem is that you want to calculate something on Case Level with two 1:N relationships on the cases, but your dimension is on Activity Level with no direct link to your Planned Time Table. I’m currently in feedback with your Data Scientist on how to solve your problem.

In the mean time could you try this code as KPI:

CASE “ACTIVITIES”.“ACTIVITY_EN”

WHEN IN (‘A’,‘B’,‘C’) THEN

SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Setup”)) - SUM(PU_SUM(“CASES”, HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”), “ACTIVITIES”.“ACTIVITY_EN” IN (‘A’,‘B’,‘C’)))

WHEN IN (‘D’,‘E’,‘F’) THEN

SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Setup”)) - SUM(PU_SUM(“CASES”, HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”), “ACTIVITIES”.“ACTIVITY_EN” IN (‘D’,‘E’,‘F’)))

WHEN IN (‘G’,‘H’,‘I’) THEN

SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Setup”)) - SUM(PU_SUM(“CASES”, HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”), “ACTIVITIES”.“ACTIVITY_EN” IN (‘G’,‘H’,‘I’)))

WHEN IN (‘L’,‘M’,‘N’) THEN

SUM(PU_SUM(“CASES”,“PLANNED_TIME”.“Setup”)) - SUM(PU_SUM(“CASES”, HOURS_BETWEEN(“ACTIVITIES”.“START_TIME”,“ACTIVITIES”.“END_TIME”), “ACTIVITIES”.“ACTIVITY_EN” IN (‘L’,‘M’,‘N’)))

END

In the case that the values are multiples of the value that should logically result out of the formula, try dividing the sum of each statement by

SUM(PU_COUNT(“CASES”, ACTIVITIES”.“ACTIVITY_EN”, ACTIVITIES”.“ACTIVITY_EN” IN (‘’,‘’,‘_’)))

Best regards,

Benedict