topic Re: Convert HH:MM:SS in days for average expression in chart as table in QlikView

Convert HH:MM:SS in days for average expression in chart as table

Fri, 26 Dec 2014 05:53:35 GMT

Dear Experts,

Help required to get the MTTR average for EQUNR_V in Days.

MTTR is in HH:MM:SS now i need to get average in days.

In Load statement MTTR is calculated as below.

Interval((Date#(AUSBS,'M/D/YYYY')+time(AUZTB,'HH:mm:SS'))-(Date#(AUSVN,'M/D/YYYY')+time(AUZTV,'HH:mm:SS')),'hh:mm') as MTTR,

EQUNR_V	EQM_AUSVN	EQM_AUZTV	EQM_AUSBS	EQM_AUZTB	MTTR
21220	2014-05-24	10:42:24	2014-05-24	17:45:50	7:03:26
21220	2014-05-31	12:15:45	2014-05-31	14:29:30	2:13:45
21220	2014-06-10	15:12:25	2014-06-10	16:05:25	0:53:00
21220	2014-08-12	09:35:33	2014-08-12	10:55:00	1:19:27
21220	2014-09-07	09:11:56	2014-09-07	12:25:15	3:13:19
21220	2014-09-08	10:26:16	2014-09-08	14:25:40	3:59:24
21220	2014-09-24	10:34:29	2014-09-24	11:25:32	0:51:03
21220	2014-10-08	11:47:57	2014-10-08	16:25:22	4:37:25
21220	2014-10-13	11:01:11	2014-10-13	12:25:40	1:24:29
21220	2014-10-30	12:09:59	2014-10-30	13:00:00	0:50:01
21220	2014-11-07	10:57:11	2014-11-07	14:25:47	3:28:36
21220	2014-11-13	10:54:10	2014-11-13	11:30:14	0:36:04
21220	2014-12-03	10:41:26	2014-12-03	10:55:40	0:14:14
21220	2014-12-09	10:04:03	2014-12-09	10:30:47	0:26:44

Thanks

Ashif

Re: Convert HH:MM:SS in days for average expression in chart as table

anbu1984 — Fri, 26 Dec 2014 06:03:53 GMT

Tbl:

Load * Inline [

EQUNR_V,AUSVN,AUZTV,AUSBS,AUZTB,MTTR

21220,2014-05-24,10:42:24,2014-05-24,17:45:50,7:03:26

21220,2014-05-31,12:15:45,2014-05-31,14:29:30,2:13:45

21220,2014-06-10,15:12:25,2014-06-10,16:05:25,0:53:00

21220,2014-08-12,09:35:33,2014-08-12,10:55:00,1:19:27

21220,2014-09-07,09:11:56,2014-09-07,12:25:15,3:13:19

21220,2014-09-08,10:26:16,2014-09-08,14:25:40,3:59:24

21220,2014-09-24,10:34:29,2014-09-24,11:25:32,0:51:03

21220,2014-10-08,11:47:57,2014-10-08,16:25:22,4:37:25

21220,2014-10-13,11:01:11,2014-10-13,12:25:40,1:24:29

21220,2014-10-30,12:09:59,2014-10-30,13:00:00,0:50:01

21220,2014-11-07,10:57:11,2014-11-07,14:25:47,3:28:36

21220,2014-11-13,10:54:10,2014-11-13,11:30:14,0:36:04

21220,2014-12-03,10:41:26,2014-12-03,10:55:40,0:14:14

21220,2014-12-09,10:04:03,2014-12-09,10:30:47,0:26:44 ];

Load EQUNR_V,Interval(Avg((Date#(AUSBS,'YYYY-MM-DD')+time(AUZTB,'HH:mm:SS'))-(Date#(AUSVN,'YYYY-MM-DD')+time(AUZTV,'HH:mm:SS'))),'hh:mm') as MTTR1,Time(Avg(Time#(MTTR,'hh:mm:ss'))) Resident Tbl Group By EQUNR_V;

Re: Convert HH:MM:SS in days for average expression in chart as table

Fri, 26 Dec 2014 06:15:19 GMT

Thanks Anbu,

I Dont want in load statement. I want in chart expression Avg of MTTR in days.

Regards

Ashif

Re: Convert HH:MM:SS in days for average expression in chart as table

anbu1984 — Fri, 26 Dec 2014 06:29:32 GMT

Dimension: EQUNR_V

Expression:

1.Avg(MTTR)

2.Avg((Date#(AUSBS,'YYYY-MM-DD')+time(AUZTB,'HH:mm:SS'))-(Date#(AUSVN,'YYYY-MM-DD')+time(AUZTV,'HH:mm:SS')))

Re: Convert HH:MM:SS in days for average expression in chart as table

MarcoWedel — Fri, 26 Dec 2014 08:55:05 GMT

Dimension:EQUNR_V

Expression: Interval(Avg(MTTR),'d hh:mm:ss')

Re: Convert HH:MM:SS in days for average expression in chart as table

jagan — Fri, 26 Dec 2014 10:05:19 GMT

Hi,

Try

Avg(Interval(TimeStamp#(MTTR, 'h:mm:ss'), 'ss'))/86400

Regards,

Jagan.

Re: Convert HH:MM:SS in days for average expression in chart as table

Fri, 26 Dec 2014 10:09:00 GMT

Thanks. I came across the same.