Time Format Problem in Chart (Need Days, Hours, Minutes)

[#1]

Hi community,
I have a time formatting problem.  To put it simply, I use a custom database function to compare two dates for very order such as order date and close date and return the difference in business days, hours, minutes seconds.  The return value (Difference) is loaded into the dashboard via the load script. However note that the return format of this function is as follows:'

For example

Order_Code            Order Date            Closed Date               Difference

1234                       2014-03-20 15.02     2014-03-02 10.51         9 05:40:27

Which means it took 9 business days, 5 hours and 40 minutes etc to complete the order. This works fine for one order, but in a chart the difference does not aggregate for a customer like average.  Using an average function will return null at the customer total order level so it won't compute.

I've looked at the Timestamp# function but cannot make it work at the aggregate level. For example if there were ten order, what would be the average difference in days, hours and minutes.  Any ideas?

Any help would be appreciated.

Thanks.

Don Saluga

[maxgro]

maybe with

=avg(Interval#(Difference, 'D hh:mm:ss'))

then you can format in number tab (interval)

[#3]

Thanks Massimo.

I tried it but it is still returning " - " at the aggregate level.

[maxgro]

could you post some rows of your table?

[#5]

Below is the result I am getting for one customer. Note the nulls at the summary level.

Client Name	Client Code	Order Code	Order Date	HC Sent Dte	Order to Assign	Order to Appt.	Appt to Prelim Rcvd	Order to Prelim Rcvd	Order to Completion
					-	-	-	-	-
AMERICAN ADVISORS GROUP	AAPMCP	10455772	4/4/2014	4/15/2014	4  05:31:50	4  02:52:05	1  07:38:28	1  07:38:28	7  04:22:05
AMERICAN ADVISORS GROUP	AAPMCP	10465522	4/9/2014	4/23/2014	1  03:51:58	7  03:43:07	1  07:24:23	1  07:24:23	10  02:18:05
AMERICAN ADVISORS GROUP	AAIRCP	10466245	4/9/2014	4/17/2014	0  00:58:24	2  01:15:00	2  07:30:00	2  07:30:00	5  05:16:17
AMERICAN ADVISORS GROUP	AAPMCP	10466597	4/10/2014	4/17/2014	0  02:53:32	2  04:26:58	2  04:52:07	2  04:52:07	5  05:49:53
AMERICAN ADVISORS GROUP	AAPMCP	10468373	4/11/2014	4/18/2014	0  03:24:25	3  02:59:57	1  07:29:16	1  07:29:16	5  06:29:57
AMERICAN ADVISORS GROUP	AAPMCP	10479035	4/16/2014	4/21/2014	0  02:17:54	1  03:17:54	1  00:56:22	1  00:56:22	3  02:17:54
AMERICAN ADVISORS GROUP	AAPMCP	10479275	4/16/2014	4/23/2014	0  07:30:30	2  05:33:57	1  01:35:18	1  01:35:18	4  07:55:19

[#6]

I'm not sure that the day, hours, minutes and seconds can be averaged in this format.

[maxgro]

in the attachment I made an average by client code of your table

hope it helps