How to Filter Out Activities of Cases Based on the Timestamp?

I am trying to filter out activities from cases that take place earlier/later than a user given timestamp.

As of now, I have tried it with the P2P example without any success.

The steps that I took are the following:

In order to make my example simpler, I went to the Case Explorer page and chose only the CASE ID -> 143365

While clicking on this Case I saw 6 activities (Create Purchase Requisition Item, Create Purchase Order Item, Send Purchase Order, Receive Goods, Scan Invoice, Book Invoice). For my example, I wanted only to include the activities found in March. In this case, the only activity found in this month would be Book Invoice.

The way I tried to filter the rest of the activities was with the “Attribute selection”. Then I chose from the “Table And Column” dropdown the “_CEL_P2P_ACTIVITIES” -> “EVENTTIME”-> Datum of Month Marz.

When I go to the Variant Explorer or Process Explorer I still see the rest of the activities that take place previous to Marz.

In the screenshot uploaded below, you can too see the problem.

Since the activities in each case are not in the same order, the filtering should not be activity-based but time-based.

Is this even possible within Celonis?

A bypass would be filtering such activities in the Excel file. I want to avoid it though since I would have to upload different files to the cloud.

1 Like

It seems that u want to crop the process based on the time. I am looking for the solution of this problem myself. But if you just want to see which activities and how many happened in respective month i would suggest an OLAP table in a form
Activity___Number of cases___ Number of activities
Activity1_______5________________6___________
Activity2_______2________________7___________
and the date picker.

or

Round month___________Activity1_____________________Activity2______
March,2018___case Count (or activity count)___case Count (or activity count)
April, 2018____case Count (or activity count)___case Count (or activity count)

Best,
Maria

2 Likes

Thank you for responding to my question Maria.

Exactly. I want to crop based on time. The time intervals are not staying constant, so I can not always filter on a monthly basis.

For the first case of your solution:
So choose Activity as a Dimension, Number of cases and Number of activities as KPI’s?

Nevertheless, I do not know whether the OLAP table would help since I want to model and visualize the process in the variant/process explorer.

AFAIK there is no way to exclude activities from process explorer/variant explorer based on cut-off date. IMHO this should not be possible, removing just some activities from the case will result in plenty “heads” or “tails” leading to the real mess on screen if you choose more than one case.

If you want to completely remove cases which do not fit into interval you can filter by PU_FIRST and PU_LAST simultaneously.

You can also “highlight” activities happed in user defined period: create custom process KPI to return the share of activities to fit specific interval. Something like
AVG(CASE WHEN DATE_BETWEEN('activity date', <%= range_start %>, <%= range_end %> ) = 1 THEN 1.0 ELSE 0.0 END).
Use threshold to color activities with low values as “light grey” and with high values as “dark blue”. As a result you will have activities which fit into range highlighted while maintaining overall process readability :grinning:

1 Like

[EDIT: For an updated response and more information about Date Filters look here.]

Hi Salonik, DrWindy, nicks.si,

you can filter out activities in OLAP tables and the Process Explorer by mapping the activities to NULL. (Both components work a little bit differently so there is a different way for each.) In the Variant Explorer I didn’t find a way to filter based on the Eventtime or Activity Name besides using the “hiding” function in the frontend.
How it works for the others:

Process Explorer
image image
To filter out activity “skiing”, go to “Settings” and create a custom dimension that excludes the undesired activities. An elegant way is to use the REMAP_VALUES() function (new in 4.5)
REMAP_VALUES ( "Eventlog"."Activity", ['skiing', NULL])

To filter based on the time, as in your example, you can use CASE WHEN:
CASE WHEN MONTH("Eventlog"."Eventtime") = 3 THEN "Eventlog"."Activity" ELSE NULL END

OLAP table
The OLAP table is more flexible and you can just filter it:
FILTER ISNULL(REMAP_VALUES ( "Eventlog"."Activity", ['skiing', NULL])) = 0;
The ISNULL() function returns 1 or 0 if the input is NULL or not.

Does this help your problem, Salonik?
Kind regards,
Max

4 Likes

Hello Max,

the activity-based recommendations of yours are not what I am looking for.

This part though seems interesting. Could you please explain, where to write it though? I did the following steps:

Analysis settings -> Load script -> copy pasted your “code”

in the P2P example but didn’t get any response.

To that, what is the meaning of MONTH(“Eventlog”.“Eventtime”) = 3? Choosing only the activities in the month Marz??

It seems quite strange, that such a task is so hard to solve in Celonis. In Excel it is so easy to filter based on Date.

Thank you.

Hi Salonik,

Thanks for your response. Regarding your questons:

  • Check out this video how to set custom dimensions in the Process Explorer:
    https://community.celonis.com/uploads/short-url/hqzJQv0DB4WAgyVZJUtLc3NgycA.webm

  • If you are on the IBC, you can check here in the help how the MONTH function works. If the result of MONTH("…"."…") is 3, that means the month of the timestamp is March.

  • In the Process Explorer you can easily hide activities with one click in the top left. If you want more flexibility or resolve it more programatically you can use PQL. In comparison to Excel, data points in Celonis are linked (or joined). Which data point (for example an activity) belongs to which other data point (for example a case) is defined in your Data Model. If you draw the linked tables of your Data Model on your desk and you say “filter the activities”, do you mean you want to hide the activities or hide all data points connected to these activities? Either way, you can define what you want to do in PQL. To just hide activities, the approach above is good. If you want to know more about Data Model Design, check out this article on the Join Functionality.

Kind regards,
Max

Hello Max,

thank you for the video tutorial :slight_smile:

For your first dot. Again, in the video, the filtering is activity based. I just want to “hide” activities before/during/after a certain Date. This means, that the activities that are going to get hidden are not always the same.

For the second dot. The time period that I want to use for each case does not stay constant. This makes using the MONTH parameter not helpful.

I just want to hide the activities and NOT all data points connected to these.

Yes, I know that in the Process Explorer it is easy to filter activities with the icon-hidden-activities button. This kind of filtering is still activity-based and not date-based.

I guess I will just have to export my data in another format.

Hey,

instead of the REMAP_VALUES formula that I used in the video, you can insert the statement mentioned above:

CASE WHEN MONTH("Filter_Activities_xlsx_Activities"."Eventtime") = 3 THEN "Filter_Activities_xlsx_Activities"."Activities" ELSE NULL END

This will filter all cases for MONTH(…)=3, that means March. If you want to filter on a different month for every case, you need to replace the 3 with a column of for example your activity table. You could create a new column in your activity table that lists (the number of) the month you want to filter on.

Kind regards,
Max

1 Like

Still not what I am looking for. As I have already mentioned multiple times the -->>MONTH()= <<–
function is not suitable. The reason is that I sometimes need 2 Weeks, 3 Weeks or 5 Weeks. For clarification, the number of weeks is an example.

Since this is not possible in Celonis, I used my data in a way that it is possible to choose from a column: before an event, during an event, after an event. By using the Attribute selection it is visible, that such a filtering is possible, but still all the activities appear. For example, I choose for a number of cases the filtering option, during an event. All the cases that have an activity that takes place during this event appear. Still the activities that take place before this activity are also visible.

In conclusion, the easiest way to filter activities up to this point is unfortunately in Excel.

Hi Salonik,

Sorry my last responses were not clear enough. I prepared an analysis to solve your request in different scenarios. Check it out here. What it contains:

  1. Filter activities by two dates in an OLAP table
  2. Filter activities by two dates in an OLAP table giving users the possibility to write a date
  3. Filter the Case Explorer using the Load Script
  4. Filter the Process Explorer using the Custom Nodes

Here is a video how I configured all cases:

If you are an IBC user, the Date format documentation will be useful for you.

Is this what you are looking for?

@DrWindy and @nicks.si the above example might be helpful for you, too.

Kind regards,
Max

4 Likes

Hello Max,

yeeees :smile: this is it, the solution I was looking for.

For me, the most useful part is step 4.

To that, I had to use the right name of the table from which the values were read.

In my case instead of “Activities”.“Eventtime” it was “_CEL_P2P_ACTIVITIES”.“EVENTTIME” or “P2P Demo Eventlog.csv”.“EVENTTIME”.

Also I saw, that although the filter in the “Load script” is working, the filtering isn’t working properly in the Process Explorer. So only the cases that have activities within the wanted timeframe will appear in the Process Explorer but from start till the end, which means also the activities outside the timeframe. With the suggested step 4. I could see in the Process Explorer only the Activities that I want.

Thank you sooo much.

2 Likes

Hi Salonik,

I am glad I could help. Feel free to share your Process Mining experiences here in the community with the others.

Kind regards,
Max

1 Like

Is there a way to make the same Filtering in the Variant Explorer as in the Process Explorer?

In the settings of the Variant Explorer there is, unfortunately, no Process Nodes or something similar.

The global filtering is also not working properly (Analysis Settings -> load script)

1 Like

Does someone have a solution for saloniks request?