How to visualise the life of an enquiry from start to finish. Including status changes?

[jblomqvist]

Hi all,

Let's say I have an enquiry status table like this:

enquiry_number	enquiry_log_number	logged_date	officer_code	enq_status_code
1	1	2005-12-09 11:05:55.783	MKB	0020
1	2	2005-12-09 16:16:54.607	MKB	0020
1	3	2005-12-14 11:46:27.557	RW	0010
1	4	2005-12-14 11:50:22.377	RW	0010
1	5	2005-12-14 17:47:56.717	SBS	0010
1	6	2005-12-15 14:38:00.893	SBS	0020
2	1	2005-12-09 13:39:57.783	MKB	0020
2	2	2005-12-09 14:42:03.510	MKB	0020
2	3	2005-12-09 16:16:30.660	LDEA	0020
2	4	2005-12-12 16:55:32.933	LGAR	0020
2	5	2005-12-14 17:47:50.403	SBS	0020
2	6	2005-12-16 14:24:34.513	RW	0020
2	7	2005-12-16 14:24:38.983	RW	0020
2	8	2005-12-16 14:24:43.107	RW	0030
2	9	2005-12-16 14:27:45.500	SBS	0030
2	10	2006-02-10 16:41:09.433	RW	0030
2	11	2006-02-13 16:04:25.280	JBOO	0060
2	12	2006-04-18 15:17:10.390	BC	0060
2	13	2006-11-07 13:23:33.323	MW	0060
2	14	2006-11-07 13:24:42.833	RW	0060
3	1	2005-12-09 15:24:28.793	PM	0020
3	2	2005-12-09 15:53:21.527	PM	0020
3	3	2005-12-09 15:53:34.857	PM	0020
3	4	2005-12-09 15:54:17.287	IPH	0020
3	5	2005-12-14 17:47:46.917	SBS	0020
3	6	2005-12-16 14:25:16.497	SBS	0030
3	7	2006-02-10 16:41:04.867	RW	0030
3	8	2006-11-07 13:23:38.917	MW	0030
3	9	2006-11-07 13:24:38.753	RW	0030

The enquiry_log_number is recording every status change an enquiry goes through.

What I would like to do is visualise the status change each enquiry goes through in it's life from the very first log number of an enquiry to the latest log number.

I would like to see how long an enquiry status stayed between each status change. You can use logged_date field for this.

Does anyone know how I can visualise something like this? If anyone has some examples using the sample data it would be great.

[benjamins]

Hi John,

this is quite a common problem with no single correct answer.

See my attached sample.

What you need to do in any case is calculate the time between two status. This is usually done using Previous() function (in script).

The matter of visualization is a bit more difficult. I have included two sample charts. One with accumulation and one without.

Those charts become unreadable when a) a lot of issues are viewed and b) the duration between steps vary greatly.

You may want to create different classes depending on duration. Example: <1h. <4h, <8h, <1d, <3d, etc.

Or perhaps you only want to display issues with a maximum total Duration of 100 days, or, or, or the possibilities are endless.

This depends totally on your business needs.