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 Ive defined dedicated variables :
The actual data are derived from the ACTIVITIES table.
The Target data are located in another table:
image.png827211 4.92 KB
The two tables are not directly linked. The common parent table is the CASE table.
image.png1609576 21.6 KB
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?
image.png772114 6.55 KB
Thank you in advance for your support.
Kind regards,
Francesca
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.
image.png791380 6.66 KB
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, Im 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 Im 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. Im 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
