Help! Advanced Time Interval Measurements

[mb_me]

Hey again everyone... back with a new question regarding measuring time between data items.

My dataset currently consists of various Case_IDs, request_type_IDs, and timestamps.
- Every Case_ID will have various request_type_IDs as the agents within the Case interact with each other. 

So a brief high-level example is:

Case_ID	request_type_ID	request_type_description	date_of_procedure
12345678	1	customer message to  Support	5/20/2023 10:30:00
12345678	30	support response to customer, proposed measure	5/21/2023 08:00:00
12345678	3	customer replied to support that proposal not effective	5/22/2023 15:14:00
12345678	30	support response to customer, proposed measure	5/23/2023 11:15:00
12345678	5	customer replied to support that proposal effective	5/24/2023 8:00:00

So what I want to do is measure average time for support to reply to the customer, but also average time for customer to reply to support.

[rwunderlich]

You can calculate the interval between rows with something like:

LOAD
  *,
  if (CASE_ID = Previous(CASE_ID), 
    Interval(date_of_procedure - Previous(date_of_procedure))
    , 0) as Duration,
  If (request_type_ID = 30, 'Support', 'Customer') as ResponseFrom
Resident yourdata
Order by CASE_ID, date_of_procedure

You may have to add a where clause to include only the request_type_ID you are interested in.

If ResponseFrom is a dimension in your chart, you can calculate Interval(Avg(Duration)).

If not using the dimension (like in a KPI), you can calculate for each ResponseFrom with:

Interval(Avg({<ResponseFrom={'Support')}>}Duration))

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[mb_me]

Thank you for the response!
Maybe to help guide the support with a more direct example, the expression we currently use is:

=avg(Round(Aggr(Min({<request_type_id={21,31,32,132,131}>} creat_ts)-Min( {<request_type_id={1}>} creat_ts),case_id)*24,0.01))

<request_type_id={21,31,32,132,131}> gives me the transaction types that support can give to the customer.
<request_type_id={1}> is the transaction type for customer opening a support case.
<request_type_id={10}> is the transaction type for the customer saying support still required.

I understand the Min() function gives me the first occurring transaction type and it's timestamp...
and the Aggr() function is allowing me to aggregate the expression on each individual case_id.

What I struggle with is when I remove the Min() function in the equation... I figure this would allow me to look at all time intervals between these transaction types for each case, and then average them... but when I do so, the result is null (which isn't plausible for this scenario... so I know something isn't working right).

[joshsiddle8]

To calculate the average response times between customers and support, you can follow these steps:

Sort your data by Case_ID and date_of_procedure to ensure chronological order within each case.

Create two additional columns:

One for response_time_to_customer
Another for response_time_to_support
Calculate the response times based on request_type_ID. Here's how you can approach it:

When request_type_ID is 30 (support response to customer), calculate the time difference from the previous customer message (e.g., request_type_ID 1 or 3).
When request_type_ID is 3 or 5 (customer reply to support), calculate the time difference from the previous support response (e.g., request_type_ID 30).
Calculate averages of these response times.

Here's an example of how to do it in Python using pandas:

python
Copy code
import pandas as pd

# Sample data
data = {
'Case_ID': [12345678, 12345678, 12345678, 12345678, 12345678],
'request_type_ID': [1, 30, 3, 30, 5],
'request_type_description': [
'customer message to Support',
'support response to customer, proposed measure',
'customer replied to support that proposal not effective',
'support response to customer, proposed measure',
'customer replied to support that proposal effective'
],
'date_of_procedure': [
'5/20/2023 10:30:00',
'5/21/2023 08:00:00',
'5/22/2023 15:14:00',
'5/23/2023 11:15:00',
'5/24/2023 08:00:00'
]
}

df = pd.DataFrame(data)
df['date_of_procedure'] = pd.to_datetime(df['date_of_procedure'])

# Initialize new columns
df['response_time_to_customer'] = None
df['response_time_to_support'] = None

# Calculate response times
for i in range(1, len(df)):
if df.loc[i, 'request_type_ID'] == 30:
prev_index = df.loc[:i-1][df['Case_ID'] == df.loc[i, 'Case_ID']].index[-1]
if df.loc[prev_index, 'request_type_ID'] in [1, 3]:
df.loc[i, 'response_time_to_customer'] = df.loc[i, 'date_of_procedure'] - df.loc[prev_index, 'date_of_procedure']
elif df.loc[i, 'request_type_ID'] in [3, 5]:
prev_index = df.loc[:i-1][df['Case_ID'] == df.loc[i, 'Case_ID']].index[-1]
if df.loc[prev_index, 'request_type_ID'] == 30:
df.loc[i, 'response_time_to_support'] = df.loc[i, 'date_of_procedure'] - df.loc[prev_index, 'date_of_procedure']

# Convert response times to seconds for averaging
df['response_time_to_customer'] = df['response_time_to_customer'].dt.total_seconds()
df['response_time_to_support'] = df['response_time_to_support'].dt.total_seconds()

# Calculate averages
avg_response_time_to_customer = df['response_time_to_customer'].dropna().mean()
avg_response_time_to_support = df['response_time_to_support'].dropna().mean()

print(f'Average response time for support to reply to customer: {avg_response_time_to_customer / 3600:.2f} hours')
print(f'Average response time for customer to reply to support: {avg_response_time_to_support / 3600:.2f} hours')
This script calculates the response times and averages for support and customer replies. Make sure your actual dataset is loaded into the data dictionary or read directly from a file.