Hi All,
Repeat Caller Logic within 48 hours . (Repeat caller duration is based on callstartdate and prevcallsegstartdate as per the example provided)
Formula: Numerator - Unique Contact, Denominator - Unique + Repeat Contact
| CustomerPhone | CallID | Callstartdate | Callenddate | prevcallstartdate | repeatcalldurationSecs |
| 123 | Call1 | 2020-06-08 11:13:05.000 | 2020-06-08 11:28:48.000 | NULL | NULL |
| 123 | Call2 | 2020-06-08 11:39:35.000 | 2020-06-08 11:50:23.000 | 2020-06-08 11:13:05.000 | 1590 |
| 123 | Call3 | 2020-06-11 15:24:07.000 | 2020-06-11 15:33:56.000 | 2020-06-08 11:39:35.000 | 272672 |
| 126 | Call12 | 2020-03-22 14:40:49.000 | 2020-03-22 15:09:42.000 | NULL | NULL |
From the above example - In the case of 123 phone number (1-2 row number) - its a repeat contact.
This customer contacted twice within 48 hours based on the first two rows
Now in the numerator - it should be 0
in the denominator - it should be 2
3rd row - again the same customer contacted after 48 hours, it should treat as a new customer
Now numerator - 1 and denominator - 1
In case of 126 customer phone number - its unique contact
Now numerator - 1
denominator - 1
Total KPI value - Numerator - 0+1+1
Denominator - 2+1+1
Can someone help me how I can achieve this in Qlik?
.png "Former Employee"){kind=icon}
