Skip to main content
Question

How to cumulate the number of items in a given status per week

  • October 28, 2021
  • 2 replies
  • 5 views
A
  • Anonymous

I have a column chart component which displays for each status(activity) the number of cases for which this activity is the last per week. So in dimensions I have this kind of expression ROUND_WEEK("ACTIVITIES"."EVENTTIME") but I want to display for each week for each status(activity) the number of cases for which this activity is the last and from a week to another I want to sum the cases which have not moved to another activity. Is there a way to do that, so in dimensions I want to have a kind of calendar of all weeks and then according to the date of the activities I want to display what I explained above.

 

Thanks in advance for your help !  

2 replies

S
Level 0

Hi Jean-Christophe,

 

Although this does not sound like a typical Celonis component to build, you might be able to approach the results with these ideas:

  • using CASE WHEN statements to check if Status/Activity A is the last activity for each case, with syntaxes such as these:

CASE WHEN

PU_LAST("Cases","_CEL_ACTIVITIES"."EVENTTIME")

=

PU_LAST("Cases","_CEL_ACTIVITIES"."EVENTTIME"

,"_CEL_ACTIVITIES"."ACTIVITY_EN" = 'A')

THEN 1.0

ELSE 0.0

END

  • Using Window PQL functions like RUNNING_TOTAL
  • If the frontend tools don't suffice then the go-to is the Backend: update the tables themselves within Event Collection, to have more columns, so that you can more easily achieve your result in the frontend. For example maybe you could add the Status A Exit Date of each case on the Case table, so that you can directly use the Enter and Exit dates within the frontend, for example with a BETWEEN-type function to detect if a case is still stuck on Status A at any point in time.

 

Overall would also recommend to start working on this for just 1 Status/Activity. And once that works, replicate for other Statuses/Activities.

A
  • Anonymous
  • November 2, 2021

Hi Stephane,

 

Thank you for your answer !

In fact the column chart component is configured like that :

  • Dimensions : ROUND_WEEK("ACTIVITIES"."EVENTTIME")
  • KPIS (for example one status/activity): COUNT(CASE WHEN "_CEL_ACTIVITIES"."ACTIVITY_EN" = 'A' THEN "cases"."id1" || "cases"."id2" ELSE NULL END)
  • filter component : FILTER "_CEL_ACTIVITIES"."EVENTTIME" = PU_MAX("cases", "_CEL_ACTIVITIES"."EVENTTIME");

So this displays  for each status(activity) the number of cases for which this activity is the last per week but there is not the sum from a week to another. How can I use RUNNING_TOTAL function in my case ?

Community Code of ConductAccessibility statement