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 :smiley:
Thank you for your help,

Best Regards
Robin

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 don’t know is how to compute these 2 dimensions together.

Let’s 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

image

Thank you for your help,

Best Regards
Robin

Hi Robin,

What error did you get when you tried to merge it into one chart?

I’ve 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)

Backlog_KPI

In this case, both “created” and “closed” dates were in the same table, so I’m not sure what it will look like with two tables - it will also depend on how your data model is setup, so that you don’t face issues like “no common parent table”.

I hope that was helpful!

Best,
João