Number of cases = MEDIAN Throughput Time


#1

Dear all,

I am currently trying to count the number of cases which equal the median throughput time value.
The intention is to tell how many cases exist in the data set which meet the median.

EXCEL

CELONIS


COUNT(CASE WHEN
MEDIAN(CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Create Other Delivery Document’] TO FIRST_OCCURRENCE[‘Record Goods Receipt’],
REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".“EVENTTIME”, DAYS)))
= CALC_THROUGHPUT(FIRST_OCCURRENCE[‘Create Other Delivery Document’] TO FIRST_OCCURRENCE[‘Record Goods Receipt’],
REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".“EVENTTIME”, DAYS)) THEN 1 ELSE 0 END)


#3

Have you tried to use NULL instead of 0 or SUM instead of the Count?


#4

Hi there,

I was thinking about which problem you want to solve with your methodology. I personally do not like these median calculation at all. Since they will tell you nothing about the variance/spread of the throughput time.
When you have space, I can strongly recommend Histogram with 10 buckets where you can drill down.Or in case you want to keep your calculation always take the variance with it.
Good luck!!
Best Regards,
Hans


#5

Thanks a lot for your responses.

Your are fully right that the MEDIAN itself is not a profound key figure.
I wanted to start with the described issue to get familiar with the “basics”.

Ultimately I want to put the Median / a value range in relation to the % of the cases.
Example: Median = 30 reflecting up to 50% of the cases. The value 33 reflects up to 80% of the cases.

Any other insights or examples are highly welcome.


#6

Dear Schneith,

Mathematical seen the median is the median.I presume you want to find out which which kind of deviation/variance of the median you cover a % of cases. This can still be a tough topic.
Example to keep it simple: say you have 100 cases.
50 have a TPT of 10 days, the rest 30 days. The median will be 20. So what will this tell you? Maybe this is even OK in terms of OTIF.
What we have done in our cockpit to try to understand this spread. A tabel with a double drop-down in the dimension will show you where the spread is allocated.
Normally you want to see this in a meaningful context meaning certain products, processes, customer, net values etc., Etc.
The next step would be to nail down late deliveries. We use the last confirmation date versus last date in schedule lines. The last confirmed date we use to define a “Placeholder activity” e.g. “Delivery too late”. This is extremely handy to do all further calculations of even running cases. (without delivery).
As said to understand the spread better use a visual Histogram…
Have fun!

Best Regards
Hans