Open cases at the end of the month


#1

Hello,

I am aiming for calculating the ‘open cases’ at the end of the month, i.e. want to check how many cases at the end of each month didn’t have the ‘closing activities’ (say A, B or C). It is possible that the CASE-ID is closed lets say in May, 2017 however I need to see that it was still open in March,2017 and April, 2017. Could you help me with the implementation please.

Best,
Maria


#3

Hello Maria,

If you want to look at the cases that are currently open, I recommend that you use something like

SUM(CASE WHEN PROCESS EQUALS ACT 1 END OR PROCESS EQUALS ACT2 END THEN 0 ELSE 1 END)

In a single KPI. The issue with displaying this metric over time is that cases can have activities in several months, which will be an issue when breaking them up by the dimension rows of eventtime. What comes closest will be something like:

COUNT(DISTINCT Activities.CASE_KEY) -

COUNT(DISTINCT
CASE WHEN (PROCESS EQUALS ‘Closing Activity A’ END
AND “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Closing Activity A’)

OR (PROCESS EQUALS ‘Closing Activity B’ END
AND “_CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Closing Activity B’)

OR (PROCESS EQUALS ‘Closing Activity C’ END
AND "YOUR ACTIVITY TABLE " = ‘Closing Activity C’)

THEN “YOUR ACTIVITY TABLE”."_CASE_KEY"
ELSE NULL
END)

And Eventtime from the activity table as dimension. However you need to be aware that this will not include the cases which 1. are open and 2. did not have an activity in the individual month.

Best,
Henry


#4

Hallo Maria,

I was wondering about your request and the purpose of it. We only are interested in open and closed cases but for following reason. The topic is that all the KPI your are calculating must be comparable. This is why we have a drop-down in the sheetfilter saying open or closed cases. We even have a drop-down where the user can choose from a list of activities which are seen as closed. Depending on how many process types you have in your datamodel, this can be totally different…

Have fun!
Best regards,
Hans