Skip to main content
Question

Rework Selection as column chart component in Daslhboard

  • April 5, 2024
  • 4 replies
  • 9 views
L
Level 2

Hello everyone, I'm still quite new to Process Mining and PQL.

 

How can I create the Column chart of the "Rework Selection" as a standalone component for the dashboard? In my process, there is a process flow (loop) that can occur either 0, 1, 2, 3, 4, or 5 times per case. Therefore, on the X-axis, the number of loops should be listed (0, 1, 2, 3, 4, 5), and on the Y-axis, the frequency of occurrence of each number of loops should be shown.

 

I tried it with this: https://docs.celonis.com/en/calculations-on-activity-sequences.html

 

 

And that works well for an OLAP table, but it only provides me with the count of loops per case and not the count of cases for each loop count (0,1,2,3,4,5). However, I cannot or do not know how to access this OLAP table in another component and sum up the counts per loop iteration.

 

imageThanks for your help! 😊

4 replies

dennis.pflug
Level 10

Hi Lukas,

 

I think the biggest problem will be the visibility of the bar chart because based on how many different Activities you want to display within your bar chart and for each activity showing the different numbers of rework might flood the whole chart.

 

When you say it can only be between 0-5 loops you can use a logic like this as Dimension:

 

CASE WHEN PU_COUNT("BSEG","_CEL_AP_ACTIVITIES"."ACTIVITY_EN") = 1 THEN '1'

WHEN PU_COUNT("BSEG","_CEL_AP_ACTIVITIES"."ACTIVITY_EN") = 2 THEN '2'

WHEN PU_COUNT("BSEG","_CEL_AP_ACTIVITIES"."ACTIVITY_EN") = 3 THEN '3'

WHEN PU_COUNT("BSEG","_CEL_AP_ACTIVITIES"."ACTIVITY_EN") = 4 THEN '4'

WHEN PU_COUNT("BSEG","_CEL_AP_ACTIVITIES"."ACTIVITY_EN") = 5 THEN '5'

ELSE '0' END

 

And a Activity or Case Count as KPI:

COUNT("_CEL_AP_ACTIVITIES"."ACTIVITY_EN")

 

This will show you a bar chart with x-axis =  number of loops and y-axis = frequency of occurrence

But it would make sense to combine this chart with a drop down button where someone needs to select an activity first - same way as it is done in rework selection.

 

BR

Dennis

L
Level 2
  • lukas.muell
  • Author
  • Level 2
  • April 5, 2024

Hi Dennis,

 

Thanks for your answer and help!

 

I have adopted your PQL statement. I have used the domain table because I am only using one Excel file as an event log and there are no other tables.

You mentioned visibility is an issue due to the number of activities. I would like to display only one activity, namely Activity 'E', the last one in the loop see image named Excel.

 

CASE WHEN PU_COUNT(DOMAIN_TABLE ( "abc_Sheet1"."AKTIVITÄT"),"abc_Sheet1"."AKTIVITÄT") = 1 THEN '1'

WHEN PU_COUNT(DOMAIN_TABLE ( "abc_Sheet1"."AKTIVITÄT"),"abc_Sheet1"."AKTIVITÄT") = 2 THEN '2'

WHEN PU_COUNT(DOMAIN_TABLE ( "abc_Sheet1"."AKTIVITÄT"),"abc_Sheet1"."AKTIVITÄT") = 3 THEN '3'

WHEN PU_COUNT(DOMAIN_TABLE ( "abc_Sheet1"."AKTIVITÄT"),"abc_Sheet1"."AKTIVITÄT") = 4 THEN '4'

WHEN PU_COUNT(DOMAIN_TABLE ( "abc_Sheet1"."AKTIVITÄT"),"abc_Sheet1"."AKTIVITÄT") = 5 THEN '5'

ELSE '0' END

 

The excel file (Excel) and DFG (DFG) looks as follows:

ExcelDFGThe bar chart looks as follows with the code:

bar chartThe bar chart indicates that there is only the possibility of no loop iteration, which occurs 251 times, even though there are only 100 cases.

 

However, the rework selection bar chart for the aktivity "E" looks as follows and the total occurrences also add up to the 100 case IDs.

rework selection Thanks for your help and have a nice weekend!

 

Best regards

 

Lukas😃

L
Level 2
  • lukas.muell
  • Author
  • Level 2
  • April 8, 2024

Hello everyone,

 

I created the exact same bar chart from rework selection as component with the following dimension and KPI:

 

Dimension: CALC_REWORK ( "columnTable"."AKTIVITÄT" = 'E' )

 

KPI: COUNT_TABLE("abc_Sheet1_CASES")

 

Best regards

 

Lukas

dennis.pflug
Level 10

Hi Lukas,

 

good solution.

Thanks for letting us know.

 

BR

Dennis

Community Code of ConductAccessibility statement