Forum Discussion
Figuring counts based on time frame.
So I figured this would be simple but the answer has alluded me. I have a table that holds call informaiton. One column is for the Telephone Number of the caller, one is the Queue the caller call...
The goal is to identify if a tel# has called back in within a certain timeframe (3 weeks) and if it has then to count it. Ultimately this will be used to figure out a resolution effectiveness KPI. The theory is if a customer had to call back in then the issue was not resolved.
Sample Data
| CallDate | Phone | PhoneQueue |
| 1/1/2019 | 789-555-0184 | CustService |
| 1/1/2019 | 344-555-0181 | Billing |
| 1/1/2019 | 264-555-0164 | TechSupport |
| 1/1/2019 | 727-555-0190 | Billing |
| 1/1/2019 | 273-555-0181 | TechSupport |
| 1/1/2019 | 429-555-0145 | CustService |
| 1/1/2019 | 334-555-0146 | CustService |
| 1/1/2019 | 927-555-0198 | CustService |
| 1/1/2019 | 221-555-0141 | CustService |
| 1/1/2019 | 539-555-0142 | CustService |
| 1/2/2019 | 206-555-0195 | Billing |
| 1/2/2019 | 250-555-0127 | Billing |
| 1/2/2019 | 661-555-0168 | CustService |
| 1/2/2019 | 426-555-0181 | CustService |
| 1/2/2019 | 159-555-0190 | Billing |
| 1/2/2019 | 292-555-0128 | TechSupport |
| 1/2/2019 | 987-555-0126 | CustService |
| 1/3/2019 | 397-555-0144 | Billing |
| 1/3/2019 | 119-555-0144 | Billing |
| 1/3/2019 | 652-555-0115 | CustService |
| 1/3/2019 | 383-555-0111 | CustService |
| 1/3/2019 | 752-555-0187 | TechSupport |
| 1/3/2019 | 428-555-0176 | TechSupport |
| 1/3/2019 | 884-555-0193 | CustService |
| 1/3/2019 | 773-555-0182 | CustService |
| 1/3/2019 | 542-555-0164 | Billing |
| 1/3/2019 | 941-555-0155 | Billing |
| 1/3/2019 | 140-555-0143 | CustService |
| 1/3/2019 | 945-555-0112 | Billing |
| 1/3/2019 | 539-555-0182 | Billing |
| 1/4/2019 | 889-555-0176 | CustService |
| 1/4/2019 | 859-555-0173 | CustService |
| 1/4/2019 | 119-555-0167 | Billing |
| 1/4/2019 | 115-555-0113 | Billing |
| 1/4/2019 | 937-555-0136 | CustService |
| 1/4/2019 | 680-555-0192 | CustService |
| 1/4/2019 | 219-555-0126 | Billing |
| 1/4/2019 | 839-555-0198 | TechSupport |
| 1/4/2019 | 551-555-0155 | CustService |
| 1/4/2019 | 188-555-0152 | Billing |
| 1/4/2019 | 767-555-0151 | CustService |
| 1/4/2019 | 394-555-0176 | Billing |
| 1/4/2019 | 820-555-0166 | Billing |
| 1/4/2019 | 358-555-0188 | CustService |
| 1/4/2019 | 819-555-0186 | CustService |
| 1/5/2019 | 175-555-0198 | TechSupport |
| 1/5/2019 | 752-555-0185 | TechSupport |
| 1/5/2019 | 572-555-0156 | CustService |
| 1/5/2019 | 138-555-0156 | CustService |
| 1/5/2019 | 143-555-0113 | Billing |
| 1/5/2019 | 147-555-0192 | Billing |
| 1/5/2019 | 835-555-0116 | CustService |
| 1/5/2019 | 722-555-0169 | Billing |
| 1/5/2019 | 576-555-0171 | Billing |
| 1/5/2019 | 156-555-0187 | CustService |
| 1/5/2019 | 706-555-0140 | CustService |
| 1/5/2019 | 754-555-0134 | Billing |
| 1/5/2019 | 899-555-0134 | Billing |
| 1/5/2019 | 169-555-0178 | CustService |
| 1/5/2019 | 698-555-0183 | CustService |
| 1/5/2019 | 429-555-0145 | CustService |
| 1/5/2019 | 916-555-0137 | Billing |
| 1/5/2019 | 158-555-0188 | TechSupport |
| 1/5/2019 | 362-555-0121 | CustService |
| 1/6/2019 | 539-555-0121 | Billing |
| 1/6/2019 | 962-555-0144 | CustService |
| 1/6/2019 | 716-555-0123 | Billing |
| 1/6/2019 | 674-555-0188 | Billing |
| 1/6/2019 | 186-555-0135 | CustService |
| 1/6/2019 | 847-555-0151 | CustService |
| 1/6/2019 | 425-555-0158 | TechSupport |
| 1/6/2019 | 185-555-0190 | TechSupport |
| 1/6/2019 | 691-555-0149 | CustService |
| 1/6/2019 | 834-555-0132 | CustService |
| 1/6/2019 | 962-555-0112 | Billing |
| 1/6/2019 | 753-555-0179 | Billing |
| 1/6/2019 | 554-555-0124 | CustService |
| 1/6/2019 | 156-555-0187 | CustService |
| 1/6/2019 | 252-555-0127 | Billing |
| 1/6/2019 | 581-555-0172 | Billing |
| 1/6/2019 | 153-555-0114 | CustService |
| 1/6/2019 | 957-555-0125 | CustService |
| 1/6/2019 | 941-555-0155 | Billing |
| 1/6/2019 | 329-555-0183 | Billing |
| 1/6/2019 | 475-555-0188 | Billing |
A couple sample ideas on how to flag those telephone numbers:
| Month/Que | TelNumber | DupFlag |
| Jan | ||
| CustService | ||
| 429-555-0145 | 1 | |
| 156-555-0187 | 1 | |
| Billing | ||
| 941-555-0155 | 1 |
| Queue | Jan |
| CustService | 94% |
| Billing | 98% |
| TechSupport | 92% |