Count number of “open” SOURCE-TARGET-connections per day

Hi Patrick,

I've got a solution for your problem:

In an OLAP Table get the Eventtime as Dimension with this PQL Code: ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME").

You can also filter the component on Eventtime if you only want a certain range of days displayed.

As KPI in the OLAP table, the following PQL Code should display you the number of Cases on the given date (Dimension in the OLAP) which at that day are between your two specified activities:

COUNT( DISTINCT CASE WHEN PROCESS EQUALS 'Your Starting Activity Name' to ANY to 'Your Ending Activity Name' 

THEN "_CEL_O2C_ACTIVITIES"."_CASE_KEY" ELSE NULL END).

Hope this helps!

Best,

Florian

Hi Patrick,

you could also Use SOURCE()-TARGET() + Case Counts in a table. What's important is to understand which cases you want to count with regards to the timestamp. I would recommend using the EVENTTIME of the SOURCE Activity. You can use this in a CASE WHEN Statment to count the specific cases.

Check out documentation here: https://confluence.celonis.com/display/PQLdevelopment/SOURCE+-+TARGET

Best

Kevin

Hi Patrick,

none of these two solutions alone will bring the desired result. For example, SOURCE(eventtime) nor ROUND_DAY("_CEL_O2C_ACTIVITIES"."EVENTTIME") wont show you any record for 02.01.2021, so you need to use RANGE_APPEND as well

Best,

Gabriel

Hi everyone,

thanks a lot for your replies! I have tried Florian's solution combined with Gabriel's hint for RANGE_APPEND. Result does not differ from a simple case count:

COUNT( DISTINCT 

 CASE WHEN 

 PROCESS EQUALS 'A' to ANY to 'B' 

 THEN "Testlog_count_cases_between_activities_csv"."CASE" 

 ELSE NULL END

)

I have also tried using SOURCE/TARGET as suggested by Kevin, but I am not even close to a solution. Seems I can't really get my head around it. Could you help with a more specific formula?And how do I registerto get access to the Confluence page?

Thanks!

Patrick