Requirement is to show how long this selection was selected

[#1]

Customer contact - average time between updates - drop downs for each status start -> end times.

The requirement is to show how long this selection was selected for... and then average this across all customers.  So if I do one example. I select 'First customer contact - Customer Call / Email' then I choose 'another one' we would like to know. How long was it selected for based on the date entered by the user in the system, this shows us how long was this item outstanding for.  What complicates this is that the statuses can be selected in any order and there isn't a linear step (1, 2, 3) as they may have success on step 1 or step 4 for example.

1. So. I think the only way to do it is to take each customer ID and sort by the date of request and then determine the time between updates so.

A                                                                                                           B                                                                                                            C

Customer	Contact Type	Date of Request (in order)	Calculated Field
100	First customer contact - Customer Call / Email	01/09/2015
100	Second Customer contact - Customer call	02/09/2015	1 day (difference between C2-C1)
100	Review conducted, complete documents received	12/09/2015	10 days

The Graph would then show:

As it is one day between 'First cus...' to the next ( so it was selected for 1 day) and then 10 days for the 'Second Cus...' to the next item being the selected one.

List of Contact Types:

First customer contact - Customer Call / Email

First customer contact - Customer Call failed - Email &SMS sent

Second Customer contact - Customer call

Second Customer contact - Customer call failed

Third Customer contact - Customer Call

Third Customer contact - Customer Call failed - Email &SMS sent

Fourth Customer contact - Email / Letter / PIB Messaging Sent

Fifth Customer contact - Customer Call

Fifth Customer contact - Customer Call failed

Sixth Customer contact - Email / SMS Sent

Seventh Customer contact - Email / SMS / PIB Messaging Sent

Eight Customer contact - Exit Letter Sent

Appointment / Visit scheduled - Item diarized

Awaiting Documents / Documents to be submitted

Approval Obtained to extend 30-day due date

Review conducted, partial documents received / discrepancy raised

Review conducted, complete documents received

Escalation to PSM / CDD Lead - No Response from RM

Please can any one help with a solution please....!!!1

Thanks in advance

[Clever_Anjos]

Would you mind sharing some sample data?

[#3]

Hi Clever,

Here is the sample data

customer id	contact type	date of request
100	call	14/09/15
100	letter1	15/09/15
100	letter2	20/09/15
100	letter3	21/09/15
100	letter4	18/10/15
100	letter5	19/10/15
100	letter6	20/10/15
100	letter7	01/11/15
100	fax	02/11/15
100	email	03/11/15
100	letter1	04/11/15
100	letter2	05/11/15
200	fax	16/11/15
200	email	17/11/15
200	call	01/12/15
200	letter2	02/12/15
200	letter1	03/12/15
300	letter2	04/12/15
300	letter1	05/12/15

[shair_abbas]

i think it might help

num(max(date of request))-num(min(date of request))

[#5]

Hi

I have already tried the above syntax but  how about if the data we are getting is incremented as per your syntax its for only one set of data. If we see the table the customer ID changes.

Please can you further think of

Thanks in advance..!!

[ogster1974]

try aggregating/grouping the measure by customer id something like

AGGR(num(max(date of request))-num(min(date of request)), [Customer id])

Regards

Andy