topic Re: Time Format Problem in Chart (Need Days, Hours, Minutes) in QlikView

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

Thu, 03 Jul 2014 19:03:07 GMT

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

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

maxgro — Thu, 03 Jul 2014 19:20:49 GMT

maybe with

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

then you can format in number tab (interval)

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

Thu, 03 Jul 2014 19:33:27 GMT

Thanks Massimo.

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

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

maxgro — Thu, 03 Jul 2014 19:38:13 GMT

could you post some rows of your table?

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

Thu, 03 Jul 2014 19:59:03 GMT

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

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

Thu, 03 Jul 2014 20:00:02 GMT

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

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

maxgro — Thu, 03 Jul 2014 20:19:22 GMT

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

hope it helps