# Custom throughput time metrics

> With customizing data transformations and dashboard editing you can create and use custom throughput time metrics. Throughput times are the timings between two activities A and B. The following sections describe the steps you need to perform to create a custom throughput time metric when editing transformations and how to enable the througput time metric in the process app dashboards.

## Introduction

With customizing data transformations and dashboard editing you can create and use custom throughput time metrics. Throughput times are the timings between two activities A and B. The following sections describe the steps you need to perform to create a custom throughput time metric when editing transformations and how to enable the througput time metric in the process app dashboards.

## Creating a custom throughput time metric with editing transformations

You must first caclulate the throughput time and then make it available as a Case field.

### Calculating the throughput time

Per case, you can compute throughput times between **Activity A** and **Activity B**. Since activities can occur more than once per case, you need to take into account whether you take the first or last occurrence of an activity.

1. Create an additional model based on the **Event log** to calculate the desired throughput times. For example, Cases_with_throughput_times.
2. In this model, create pre-processing tables in which you define which event ends you want to use for the computations. Per table, you need the Case ID, and the Event end of an activity. The following code shows an example of how to select the last occurrence of activity A for a case.
   ```
   Event_end_activity_A as (
       select
           Event_log."Case_ID",
           max(Event_log."Event_end") as "Event_end_activity_A"
       from Event_log
       where Event_log."Activity" = 'Activity A'
       group by Event_log."Case_ID")
   ```
   :::note
   In this example, if you want to select the first occurrence of the activity, replace max with min.
   :::
3. Define the throughput time table by joining the pre-processing tables to the **Event log** and calculating the actual throughput time.
   :::tip
   You can use the datediff function provided in the pm-utils package to compute the time difference between any two event ends.
   :::
The throughput time should be computed in milliseconds for the instances where **Activity A** precedes **Activity B**. Milliseconds are the unit of time used to define durations in the app template. As the throughput times are already grouped per case in the pre-processing tables, you can pick any record. In the above example he aggregation min is used. The throughput time table, selecting the throughput time and a Case ID, can be defined as displayed in the follwing code block.

   ```
   Cases_with_throughput_times as (
       select
           Event_log."Case_ID",
           case
               when min(Event_end_activity_A."Event_end_activity_A") <= min(Event_end_activity_B."Event_end_activity_B")
                   then {{ pm_utils.datediff('millisecond',
                   'min(Event_end_activity_A."Event_end_activity_A")',
                   'min(Event_end_activity_B."Event_end_activity_B")') }}
           end as "Throughput_time_activity_A_to_activity_B"
       from Event_log
       left join Event_end_activity_A
           on Event_log."Case_ID" = Event_end_activity_A."Case_ID"
       left join Event_end_activity_B
           on Event_log."Case_ID" = Event_end_activity_B."Case_ID"
       group by Event_log."Case_ID)"
   ```

#### Calculating throughput time in days excluding weekends

You can use the `date_from_timestamp` function provided in the pm-utils package to compute the number of days between two activities. Additionally, the `diff_weekdays` function enables you to filter out weekend days.

The following code example shows how to calculate the number of weekdays between **Activity A** and **Activity B**.

```
with Event_log as (
    select * from {{ ref('Event_log') }}
),

Activity_A as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_A"
    from Event_log
    where Event_log."Activity" = 'Receive invoice'
    group by Event_log."Case_ID"
),

Activity_B as (
    select
        Event_log."Case_ID",
        min({{ pm_utils.date_from_timestamp('Event_log."Event_end"') }}) as "Date_activity_B"
    from Event_log
    where Event_log."Activity" = 'Pay invoice'
    group by Event_log."Case_ID"
),

Total_days_minus_weekends as (
    select
        Activity_A."Case_ID",
        Activity_A."Date_activity_A",
        Activity_B."Date_activity_B",
        {{ pm_utils.diff_weekdays('Activity_A."Date_activity_A"', 'Activity_B."Date_activity_B"') }}
    -- Only compute for cases where both dates are known.
    from Activity_A
    inner join Activity_B
        on Activity_A."Case_ID" = Activity_B."Case_ID"
)

select * from Total_days_minus_weekends
```

#### Calculating throughput time in days excluding holidays

Follow these steps to calculate the throughput time in days between **Activity A** and **Activity B** excluding weekend days and holidays.

**1.** Create a `Holidays.csv` file to define the days that should be counted as holidays. The file should at least contain a record for each holiday. Use the following format:

| **Holiday** | **Date** | **Weekday** |
| --- | --- | --- |
| New year's day | 2024-01-01 | Yes |
| Easter | 2024-03-31 | No |
| .. | .. | .. |

The records in the `Holidays.csv` file are used to count the number of days that need to be excluded exclude from a date range.

**2.** Load the `Holidays.csv` file as a seed file in the **dbt** project. For detailed information, refer to the [official dbt documentation](https://docs.getdbt.com/docs/build/seeds) on jinja seeds.

**3.** Calculate the throughput time in days excluding weekends using the `date_from_timestamp` and the `diff_weekdays` functions provided in the pm-utils package as described above in **Calculating throughput time in days excluding weekends**.

**4.** Calculate the number of records that are stored in the holidays `.csv` file that fall within the given date range for each case. The following code shows an example.
```
Holidays_count as (
    select
        Total_days_minus_weekends."Case_ID",
        count(Holidays."Date") as "Number_of_holidays"
    from Total_days_minus_weekends
    left join Holidays
        on Holidays."Date" between Total_days_minus_weekends."Date_activity_A" and Total_days_minus_weekends."Date_activity_B"
    where Holidays."Weekday" = 'Yes'
    group by Total_days_minus_weekends."Case_ID"
)
```

:::note
In the above example, the filter `Weekday = 'Yes'` is used to not subtract holidays when the holiday is on a Saturday or a Sunday. This is already taken care of in the `diff_weekday` function.
:::

**5.** Subtract the computed number of holidays from the total number of days computed for each case. The following code shows an example.
```
Total_days_minus_weekends_and_holidays as (
    select
        Total_days_minus_weekends."Case_ID",
        Total_days_minus_weekends."Number_of_days" - Holidays_count."Number_of_holidays" as "Number_of_days_between_dates"
    from Total_days_minus_weekends
    inner join Holidays_count
        on Total_days_minus_weekends."Case_ID" = Holidays_count."Case_ID"
)
```

### Making the throughput time available as case field

Once the throughput time table is created this table needs to be joined to the Cases table to add the additional throughput time data as case information. To have the new throughput time field available in the dashboards, it is necessary to cast the new throughput time field to one of the custom case duration fields.

Replace one of the custom case duration lines in the Cases table that look like this:

```
{{ pm_utils.optional(ref('Cases_base'), '"custom_case_duration_1"', 'integer') }} as "custom_case_duration_1",
```

with the newly created throughput time:

```
Cases_with_throughput_times."Throughput_time_activity_A_to_activity_B" as "custom_case_duration_1",
```

The updates to the transformations for the custom throughput time metric are now done and can be imported into the app template.

## Enabling the throughput time metric in the process app dashboards

When you have created a custom throughput time in your transformations it is available in your app template as a case property under its alias. You can [customize your process app](https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/customizing-process-apps) to create a throughput time metric based on the custom throughput time you created in the transformations.

:::note
By default, a new custom duration field is added as a field of type *numeric*. Make sure to edit the field and change the **Type** of the new field to *duration*. Refer to [Data manager](https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/data-manager#data-manager).
:::

* Go to [Data manager](https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/data-manager#data-manager) and create a new metric.
* Select the custom duration field to be used for the throughput time, and select **Average** or any other desired aggregation. You can also rename the custom case duration field to your desired name in the **Data Manager**.
* Edit the application and put the new metric on the charts where you want to make it available for business users.
* [Publish](https://docs.uipath.com/process-mining/automation-cloud/latest/user-guide/publishing-dashboards) the dashboards to make the throughput time metric available on the dashboards.
  :::note
  In **Purchase-to-Pay** and **Order-to-Cash** app templates a throughput time calculation is already available in the Purchase_order_items_with_throughput_times and Sales_order_items_with_throughput_times, respectively. Custom throughput times can be added there and then be made available as a custom duration on the Purchase_order_items or Sales_order_items.
  :::