Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.