One line / column / etc. chart for created, completed, open orders

He guys,

tl;dr
how to: One chart with created & completed & open orders, with slightly different filter (including / excluding activities)

Long version
I am analyzing order data and I am interested in visualizing both: created and completed orders, on a monthly basis (The cherry on the cake would be a line with the cumulated difference between created and completed orders à “increasing amount of open orders).

It is not a problem for me to build a Column Chart for created and completed orders (separated):

Created:
Dimension:

ROUND_MONTH(PU_FIRST(“S2AE_FINAL_V2A_CASES”, “S2AE_FINAL_V2A”.“EVENTTIME”))

KPI:

COUNT_TABLE(“S2AE_FINAL_V2A_CASES”)

Completed:
Dimension:

ROUND_MONTH(PU_LAST(“S2AE_FINAL_V2A_CASES”, “S2AE_FINAL_V2A”.“EVENTTIME”))

KPI:

COUNT_TABLE(“S2AE_FINAL_V2A_CASES”)


But how can I combine these to charts?
I would like to see two columns for each month, with the respective values of created and completed orders.
Created_and_Completed_Orders_one_Chart
Ideally with the line of the total amount of my open orders.
Created_and_Completed_Orders_one_Chart_with_open_orders_line

Any suggestions?
Thank you in advance,
Cheers,
Michael

1 Like

Hello Michael,

welcome to the Celonis Community!

In order to display both KPIs inside the same chart, you need to count the number of cases starting and ending for each Month.

You can achieve this with the following approach:

Dimension:

ROUND_MONTH(“S2AE_FINAL_V2A”.“EVENTTIME”)

KPI 1 (Created Cases):

COUNT(CASE WHEN PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”) = 1 THEN 1 ELSE NULL END)

PROCESS_ORDER indexes the rows of the Activity table per case, so with the formula above, we count how often there is a “first row” (start of a case) in the activity table for each month.

KPI 2 (Completed Cases):

COUNT(CASE WHEN PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”) = PU_MAX(“S2AE_FINAL_V2A_CASES”, PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”)) THEN 1 ELSE NULL END)

This is the same approach, but now we want to count the number of times where the PROCESS_ORDER result is the same as the maximum PROCESS_ORDER result for each case. That means it counts the number of times the last activity of a case falls into the month.

Hope this helps!

Best,
David

2 Likes

Hello David,

Thank you for your fast answer.
This is exactly what I needed to display created and completed orders in one Chart! Works like a charm!

May I ask you, if you have any advice how I can implement the “increasing / decreasing” of open orders?
Using “created cases” - “completed cases” only grants me the view on one month, but not the change over time -> cumulation

Created_and_Completed_Orders_one_Chart_with_open_orders_line

Cheers,
Michael

Hello Michael,

Great!
Would the “open orders” not just be the case count per month? Or how do you define “open orders”?

Best,
David

Hi David,

All cases that are created but not completed are open orders, no mather in which month they were created.
Simple example:
Berechnung_Open_Orders
Each row is a month.
In Jan 10 orders were created, but only 5 were completed. Open orders = 5
In Feb I still have 5 orders from Jan, but also 5 newly created orders from Feb… minus the 1 order which was completed in Feb. This means, end of Feb, I have 9 open orders.
I hope it is clear now :slight_smile:

Thanks again!
Michael

Hello Michael,

I needed to think about this a little :wink:

I think you can calculate this using the RUNNING_TOTAL function. You can combine both KPIs (subtract the finished orders from the created orders) and then return the running total of this number.

The query would be this:

RUNNING_TOTAL(
COUNT(CASE WHEN PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”) = 1 
THEN 1 ELSE NULL END) 
- 
COUNT(CASE WHEN PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”) = PU_MAX(“S2AE_FINAL_V2A_CASES”, PROCESS_ORDER(“S2AE_FINAL_V2A”.“EVENTTIME”)) 
THEN 1 ELSE NULL END)
)

Best,
David