Ideally with the line of the total amount of my open orders.
Any suggestions?
Thank you in advance,
Cheers,
Michael
Question
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)
Created_and_Completed_Orders.JPG495509 29.1 KB
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.
Ideally with the line of the total amount of my open orders.
Any suggestions?
Thank you in advance,
Cheers,
Michael
Ideally with the line of the total amount of my open orders.
Any suggestions?
Thank you in advance,
Cheers,
Michael
+2Hello 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
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
Cheers,
Michael
Cheers,
Michael+2Hello 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:
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 
Thanks again!
Michael
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
Thanks again!
Michael+2Hello Michael,
I needed to think about this a little 
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:
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,
DavidHi David,
I needed a while to get back to this topic and I needed to add some additional filter to the second count part (so only cases that are really completed are counted), but now it works great
careful he is a hero.jpg800450 94 KB
Thanks 4 your help,
Cheers
Michael
Hi @Michael_Berger
can I ask you something - how do you know if the case is completed or not?
When I look at this I read it like month of event time of the last activity of the case:
ROUND_MONTH(PU_LAST(S2AE_FINAL_V2A_CASES, S2AE_FINAL_V2A.EVENTTIME))
But how do you know that the case is already completed? Maybe there will be more activities happening later for this case?
Somehow I dont get it, will appreciate your help:)
Why I am asking - because my stakeholder wants to have a filter to only display completed cases in an analysis. And I cant understand how to create this.
Thank a lot!
Regards
Masha
Hi Masha,
In my case it is pretty easy.
The end of the process is always order finished. There is no possibility (despite a system error of course) that another activity follows order finished.
So for the analysis page with the mentioned chart I created a button whichs is loading a bookmark in which several filters are included. One of them is case flow through order finished.
I hope this helps!
Kind regards,
Michael
Thanks, I thought that there must be some final activity to make such a filter, makes total sense.
Not sure how to define such an activity for my process though, but good to know that this is the right direction of thinking!
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
Cheers,
Michael
Cheers,
MichaelHi Michael, did you ever figure out a way to count the open orders per month. I've recently been trying to do the same thing.
Hello Michael,
I needed to think about this a little
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:
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,
DavidHey David, did you manage to have any additional thoughts on Michaels requested regarding the open PO's? I'm running into the same issue and currently hunting down a solution. Thank you
+2Hey David, did you manage to have any additional thoughts on Michaels requested regarding the open PO's? I'm running into the same issue and currently hunting down a solution. Thank you
Hi @sean.lang
the above query (with the RUNNING_TOTAL) should calculate the number of open cases. Instead of PROCESS_ORDER, you should use INDEX_ACTIVITY_ORDER though (since PROCESS_ORDER has been deprecated in the meantime).
As a dimension for this KPI you would simply choose a ROUND_MONTH("Activities"."Activity") (or whatever timeunit you want).
Did you try this already?
Best
David
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.