Support centers for any organization, log several customer cases during the day. These cases are sometimes tagged with a specific status such as contact, review, and so on. Each case goes through different statuses in the workflow until it reaches closed or sign off in the queue. The following example calculates the number of cases in each status of the workflow and then makes use of the Minstring()
function to calculate the number of days passed since the oldest case logged for a particular status.
Load the following script which gives information on the cases logged at a debt collection agency:
LET vToday=num(today()); Case: LOAD CaseID ,DATE(Date#( DateLogged,'DD/MM/YYYY')) as DateLogged, Status INLINE [ CaseID,DateLogged,Status 101,01/01/2002,Advice 101,25/04/2002,Contact 101,21/06/2003,Creditors Meeting 101,24/06/2003,Draft Allocation 101,30/06/2003,Sign off 102,18/10/2009,Contact 102,28/10/2009,Advice 102,11/02/2010,Creditors Meeting 102,20/03/2010,Draft Allocation 102,30/06/2010,Review 103,11/02/2013,New Business 103,19/06/2013,Draft Allocation 104,30/06/2010,New Business 105,30/06/2010,Contact 105,11/02/2013,New Business 106,19/06/2013,Drafting 106,30/06/2010,Advice ];
Oldest case in Queue (in days)
.Case Volume
:Count(CaseID)
Oldest item in Queue (in Days)
:Num($(vToday)-(MinString({$<DateLogged=>} [DateLogged])),'#,##0')
Today's date is stored in a number format in the variable vToday()
. The MinString()
function finds the oldest value in the DateLogged
field from the total number of cases for each status. Next, we take a difference between Today()
and the minimum date for each status to get the number of days for the oldest case.
By making use of the Peek()
and Previous()
functions and using the correct sort order during load, we can determine the case volume for each change of status. For example, count of cases that went from advice to contact, contact to creditors meeting, and so on.