Need help comparing row values to the rest of the values in two fields

[tim.mc]

Hi there,

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

Phone Number Date of Call
111-111-1111 01/01/2023
222-222-2222 01/01/2023
333-333-3333 01/01/2023
111-111-1111 03/01/2023

What I want to do is assigned a value to each row according to whether or not there was a call from the same number within a certain timeframe, in this case two business days.

For example, in the dummy data numbers 222-222-2222 and 333-333-3333 would be assigned the value “FTR” for “first touch resolution”, as they only called the initial time and not again. The first instance of 111-111-1111, I would want to assign “1” to represent there was one call back within the following two business days. The second instance of 111-111-1111 I would assign “Callback” to denote that it was a continuation of an existing chain of callbacks within our 2 day threshold.

Could someone suggest a good way to do this? I know I need to search for matches to the current row’s date and phone number to the other rows in the dataset, but I’m having trouble thinking up a calculated field to do this.

[Max]

I would suggest doing this in SQL if you can.

However, in quicksight you could look to do a lag on the phone number and return the date then check it see if it’s 2 days below the original date.

ifelse(firstValue({Date},[{Date} ASC],[{phone_number},{Date}] ) > addDateTime(2,‘DD’,lag({Date},[{Date} DESC],1,[{phone_number}])) , ‘Same ID In Frame’,‘Not’)

Let me know if that works

[Jesse]

@tim.mc I think Max’s approach will work with the data you provided. Was thinking of an alternative way if that doesnt work out. If you want to label 111-111-1111 as a ‘Callback’ for all rows, and the others as ‘FTR’, this should get you there:

ifelse(
distinctCountOver(CallDate, [PhoneNum], PRE_AGG)>1, 'Callback',
'First Touch Resolution'
)