Hello, I would like to create a create a backlog KPI. (per week, calculate the amount of cases closed this week minus the amount of cases created this week) Creation date & close date are stored in 2 different fields of 2 different tables. (cases creation & closure are not actually part of the process in itself). At the moment, I can create 2 column charts: 1 chart with dimension = creation week and KPI = Count of cases1 chart with dimension = closure week and KPI = Count of cases I fail to merge this into 1 single chart, with dimension = fixed weeks and KPI = (Count of cases where closure date matches with the fixed week - Count of cases where creation date matches with the fixed week) Is there a way to do this, in the Analysis EDIT mode or through data-model configuration in HANA? I hope this was clear enough for you to be able to support me Thank you for your help, Best Regards Robin

How to create a “Backlog KPI”?

Hello,
I would like to create a create a backlog KPI.
(per week, calculate the amount of cases closed this week minus the amount of cases created this week)
Creation date & close date are stored in 2 different fields of 2 different tables. (cases creation & closure are not actually part of the process in itself).
At the moment, I can create 2 column charts:

1 chart with dimension = creation week and KPI = Count of cases
1 chart with dimension = closure week and KPI = Count of cases

I fail to merge this into 1 single chart, with dimension = fixed weeks and KPI = (Count of cases where closure date matches with the fixed week - Count of cases where creation date matches with the fixed week)
Is there a way to do this, in the Analysis EDIT mode or through data-model configuration in HANA?
I hope this was clear enough for you to be able to support me

Thank you for your help,
Best Regards
Robin

Page 1 / 1

Hi Robin,
how does your dimension fixed weeks look like? Is it another date column? I guess is will be difficult to achieve this in PQL only. It would be easier if there would be the weeks (dates) in one column only. Maybe an option can be to combine the two in one table and column, in order to only seperate them by an attribute. Then you would be able to count the cases based on the condition.
Best regards,
Celonis Data Science Team

Hi Team,
Thank you for your feedback.
Fixed weeks is not a dimension. The only dimensions we have are creation date & closure date. So we can get creation week and closure week.
However, what I dont know is how to compute these 2 dimensions together.
Lets have an example, with 3 cases for which I know creation date & closure date:
Case 1 was created week 2 and was closed week 3
Case 2 was created week 2 and was closed week 4
Case 3 was created week 2 and was closed week 2
How to create this Backlog KPI?
Week 1: 0 cases
Week 2 : 0 cases +3 created -1 closed = 2 cases
Week 3: 2 cases - 1 closed = 1 case
Week 4: 1 case - 1 closed = 0 case

Thank you for your help,
Best Regards
Robin

Hi Robin,
What error did you get when you tried to merge it into one chart?
Ive created something similar to your example in Celonis by having the following setup:
Dimension
Round Week of activities_table.event_time
KPIS:

Created
COUNT(
CASE WHEN ROUND_WEEK(cases_table.created_date) = ROUND_WEEK(activities_table.event_time)
THEN cases_table.case_key
END)
Closed
COUNT(
CASE WHEN ROUND_WEEK(cases_table.closed_date) = ROUND_WEEK(activities_table.event_time)
THEN cases_table.case_key
END)
Backlog
COUNT(
CASE WHEN ROUND_WEEK(cases_table.created_date) = ROUND_WEEK(activities_table.event_time)
THEN cases_table.case_key
END) - COUNT (CASE WHEN ROUND_WEEK(cases_table.closed_date) = ROUND_WEEK(activities_table.event_time)
THEN cases_table.case_key
END)

In this case, both created and closed dates were in the same table, so Im not sure what it will look like with two tables - it will also depend on how your data model is setup, so that you dont face issues like no common parent table.
I hope that was helpful!
Best,
Joo

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN