Count of cases with certain "status"

Hello friends!

Will you be so kind to help with one issue:

I have standard activity table: CASE_ID, ACTIVITY_NAME, EVENT_TIME

Please find in attachment a sample of table.
image

I want to make diagram chart (or OLAP Table) to show how many cases are being delivered at a certain month.

For example -

The activity “Start of delivery” for case #15 takes place on 05.01.2020 and the **next **activity “End of delivery” takes place on 10.06.2020. That mean that case #15 is being delivered on January, February, March, April, May, June.

The activity “Start of delivery” for case #16 takes place on 07.01.2020 and the **next ** activity “End of delivery” takes place on 12.04.2020. That mean that case #16 is being delivered on January, February, March, April.

So the diagram chart (or OLAP TABLE) should show following

Month Case Count

Jan 2

Feb 2

Mar 2

Apr 2

May 1

Jun 1

Will you please clarify which formulas or functions I have to use.

Thank you

Hi Mihail,

Assume we are talking about SAP source system.

As you dispaled standard activity table having list of activities ,but also there are some tables created in transformation to analyze data based on dimension and KPI(Measures).
Since activity tables consist of delivery related data there sould be LIKP and LIPS tables incase of source is SAP.

As per your requirement you want to display data for case count(no of delivery items) by month.

Note:Before or after below steps you must setup this In Process Explorer , You need to filter start procees = start delivery and End Process - End delivery to get case count between these 2 only.
Steps:-

1)Build analysis
2)Add component OLAP / Column chart.
3)Add Dimension for month (delivery date) -format month year.
4)Add KPI -
Since ,Celonis has delivered standard KIP called Case count ,which will count no of records of case table.
Here your case table must be LIPS ,because it holds delivery item related data.
Example:- Please ignore throughput time in below snap.
image

Let me know incase of any concerns.

Have a nice day.

Best regards,
Sagarkumar Darji