Hi I need to create a new formula to calculate the total number of cases by day, filtering by some condition. Some one can help me?

Hi Daisy,

For filtering in PQL I usually use CASE WHEN statements:

https://docs.celonis.com/en/case-when.html

I normally wrap this around a Count or Sum formula. Avg may also for for you.

Hi @daisy.enriq,

try this Formula:

here you will get for each day the total number of cases filtered by your condition (as Table)

PU_COUNT_DISTINCT (

  Domain_table ( ROUND_DAY ("_X_INC_SECONDARY_ACTIVITIES"."_EVENTTIME" ) ,

  "_X_INC_SECONDARY_ACTIVITIES"."CASE_ID" ,

  your condition

 )

you can add the AVG() to the formula to get the KPI (single number)

As above although, as Daisy wants to group be case and day, it will be

PU_COUNT_DISTINCT (

 Domain_table (

CASE_TABLE().CASE_KEY,

ROUND_DAY ("_X_INC_SECONDARY_ACTIVITIES"."_EVENTTIME" ) , "_X_INC_SECONDARY_ACTIVITIES"."CASE_ID" ,

 your condition

 )

Thankss , I already tested that approach but gave me an error, something about that we can not use CASE WHEN with Count aggregation.

Hi @Guillermo Gost,

yes! If @daisy.enriq wants to group more by case that should be the solution.

But @Guillermo Gost  wouldn't be right to use a PU_COUNT instead of a PU_COUNT_DISTINCT in this situation?

This formula seems to work well

PU_COUNT_DISTINCT (

  Domain_table ( ROUND_DAY ("_X_INC_SECONDARY_ACTIVITIES"."_EVENTTIME" ) ,

  "_X_INC_SECONDARY_ACTIVITIES"."CASE_ID" ,

  your condition

 )

Basically I need to get the same value that appear on the Cases per day on the Process Overview Cheet, that Celonis generate automatically. It is not matching for now, but maybe is some filter or the data model, I need to check. 

I also, tried this one :  Domain_table (

CASE_TABLE().CASE_KEY,

ROUND_DAY ("_X_INC_SECONDARY_ACTIVITIES"."_EVENTTIME" ) , "_X_INC_SECONDARY_ACTIVITIES"."CASE_ID" ,

 your condition

 )

But the results are juts 0 and 1

Thanks both for the help

Hmmm... I misread the question. Number of cases per day.

Then

PU_COUNT_DISTINCT(ROUND_DAY(activities.eventime),CASE_TABLE().CASE_ID)

(or activities.case_id)

COUNT_DISTINCT as she wants the number of cases, not the number of activities. Although if you group by day the case_table it will be the same, if for any reason you point to activities with count you will get 1 per each activity per case, I think

As a thumb rule I tend to use COUNT_DISTINCT over SUM or COUNT.

HTH

Hi Daisy,

i am glad that it worked well :)

If you want on the Cases per day just replace the above Formula of @Guillermo Gost  PU_COUNT_DISTINCT with PU_COUNT, so you will get values greater than 1

sure no problem :)

yes my abvove formula is right as it is with the distinct.

when we want to use this as Domain Table (your first response):

Domain_table (

CASE_TABLE().CASE_KEY,

ROUND_DAY ("_X_INC_SECONDARY_ACTIVITIES"."_EVENTTIME" )

we point to another question: per case, how often that same case appears on a specific day? so I think removing the disctinct is better, otherwise the result will be either 1 or 0. So the number of activities in a case is related to how often a case on a specific day appears.

I am glad anyway that we could help Daisy together. Thanks for the conversation Guillermo :)

Hi again. More one question, Since the Metric “Cases per day” on Process Overview is linked to other metrics in our analysis, we would like to ask how we get to this result, what are the formula behind the metric.

-We are using the formula : “PU_COUNT_DISTINCT “ as we discussed before, but the values are not matching, as you can see on the pictures. Both tabs have the same data model so that should not be the problem, and we are not using any filter.

Can you try with MEDIAN instead of Average and see if they match? We are assuming the Process Overview is calculating the average, but maybe is not....

Yes I already tried but is not matching

Ok then.... I made some tests... I think you will need to request info to Celonis about the Process Overview because I am afraid it is not clear what is doing....

For example, in my test:

• Process Overview:
  • Max of cases per month - 199 - 2019-Jan-01
• Basic OLAP - same date:
  • Month: 6163
  • Day (cases on 2019-Jan-01): 58

I have just used as dimension the rounded eventime from the activities table, and as KPI the standar KPI: Case_Count

Besides that... my data reaches to 2021.... but the Process Overview only shows data in the graph until Feb 2019 ... ????

Here the monthly number of cases graph, using a standard component

Thanks very much. Yes I will contact them