Need help finding the next status within a group and comparing dates

Hello!

I have a dataset sourced from our phone system that looks like this:

timestamp Operator Line Answered by Phone #
1/05/2024 11:00 AM Sam 1111-1111
1/05/2024 11:15 AM Sam 1111-1111
1/05/2024 11:16 AM Alex Alex 2222-2222
1/05/2024 12:05 PM Sam Sam 1111-1111

if the ‘Answered by’ field is blank, then the phone call was missed/not answered. I’ve added a calculated field to show the status:

timestamp Operator Line Answered by Phone # Call status
1/05/2024 11:00 AM Sam 1111-1111 Missed
1/05/2024 11:15 AM Sam 1111-1111 Missed
1/05/2024 11:16 AM Alex Alex 2222-2222 Answered
1/05/2024 12:05 PM Sam Sam 1111-1111 Answered

I want to know if any missed calls were then answered within an hour for each phone #. If yes, the ‘Missed’ status should be changed to ‘Returned’. So, I want to see the following result:

timestamp Operator Line Answered by Phone # Call status Final status
1/05/2024 11:00 AM Sam 1111-1111 Missed Missed
1/05/2024 11:15 AM Sam 1111-1111 Missed Returned
1/05/2024 11:16 AM Alex Alex 2222-2222 Answered Answered
1/05/2024 12:05 PM Sam Sam 1111-1111 Answered Answered

Could someone please help find a solution. Thank you!!

Julia

1 Like

Hi @julia_z - Welcome to AWS QuickSight community and thanks for posting the question. Couple of questions.

  1. What is your data source? is it relational database? Then we can think of write a custom sql and do self join and populate the final status.

Also the phone number needs to answered on the same date, for example if someone called today and it is not answered and the same person call next day and answered, how you are planning to maintain the status.

I am thinking of comparing the data with answered data set on the basis of phone number and date and then populate the final status, however let’s hear from other experts, tagging @David_Wong @sagmukhe @Biswajit_1993 @ErikG @duncan for their advise as well.

Regards - Sanjeeb

1 Like

Hi @Sanjeeb2022 It’s an Excel file and I’m unable to use sql.

Ideally, it would be great to consider business hours (9am - 5 pm). If someone calls today at 4:50 pm and is not answered, but then the same person calls back at 9:15 am the next day and it is answered, the status of the phone call should be ‘Returned’.

However, for simplicity, we can skip the business hours consideration and mark the unanswered 4:50 pm call as ‘Missed’.

Hello @julia_z and @Sanjeeb2022 !

This might be possible in QuickSight as a calculated using ifelse. Is it safe to assume that in the same CSV that you will have the time stamp of when a caller (same phone number) called back with a timestamp?

Hi @duncan, yes, there will also be a timestamp for when the same phone number calls back. I’m not sure how to use the ifelse function in this case, as we need to group by phone number and continue looping until we find the ‘Answered’ status and then compare the timestamps… Could you please help?

1 Like

Hi @duncan - Need your help on this, looks like it is a very interesting problem. I am also exploring how to get the desired result using calculated field with if else in a single data set. Please see if you are able to find something, please share.

Regards - Sanjeeb

Hello @Sanjeeb2022 and @julia_z !

I’m sorry about the late reply!

The big thing here is making sure that you have enough timestamps/attributes to show that the some one called in and wasn’t answered, and then either called back and was answered or someone on your team called them back and was able to reach the original caller. Can you provide a wider view of your table or what it might look like when a caller calls back?

We can circle back on the looping function, but for now this is my thinking:
Final Status:

ifelse(
dateDiff({timestampFirstCall}, {timestampSecondCall}, 'HH') <= 1 
  AND {CallStatus} = 'Missed'
    AND {AnsweredBy} = ' ', 
'Returned', 
'Missed'
)

My thought process is that we want to make sure that the timsetamps for the two calls are within an hour of each other, that the original call status is 'Missed", and that {AnsweredBy} is an empty string for the first call. What will help me is to know if you are generating new rows for when the call comes back in. Because then it will change the logic here.

Hi @duncan . Here is the wider table:

timestamp Operator Line Answered by Phone #
1/05/2024 11:00 AM Sam 1111-1111
1/05/2024 11:15 AM Sam 1111-1111
1/05/2024 11:16 AM Alex Alex 2222-2222
1/05/2024 12:05 PM Sam Sam 1111-1111
1/05/2024 1:03 PM Sam 1111-1111
1/05/2024 1:04 PM Sam 1111-1111
1/05/2024 1:05 PM Sam 1111-1111
1/05/2024 1:06 PM Sam 1111-1111
1/05/2024 1:07 PM Sam 1111-1111
1/05/2024 1:08 PM Sam 1111-1111
1/05/2024 1:30 PM outbound Sam 1111-1111
1/05/2024 2:00 PM Tom 3333-3333
1/05/2024 2:10 PM Tom 3333-3333
1/05/2024 3:10 PM Tom 3333-3333
1/05/2024 3:15 PM outbound Sam 3333-3333

We are also working on integrating this data into our data lake to enable us to use SQL. Would this be easier? How the query would look like?

Thanks!

Just to clarify:

timestamp - the time of the phone call
Operator Line - the person the customer was calling. ‘Outbound’ means call was initiated by
the operator, not the customer
Answered by - The name of the operator who answered/initiated the call.
Phone # - the client’s phone number