Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Not applicable

## ¿How to calculate average of hours and count by shift?

Hi there,

I need so urgent calculate the quantity the tickets are into the 3 diferents range/shifts and i have to calculate the average of hours. The following is my data source:

 TASK ID CREATED FINISHED 1 01/01/2016 12:00 01/01/2016 12:15 2 02/01/2016 04:00 02/01/2016 05:40 3 03/01/2016 21:00 03/01/2016 21:12 4 04/01/2016 22:00 04/01/2016 22:10 5 05/01/2016 15:00 05/01/2016 15:05 6 06/01/2016 16:00 06/01/2016 16:15 7 07/01/2016 04:00 07/01/2016 06:50 8 08/01/2016 18:00 08/01/2016 18:04 9 09/01/2016 13:00 09/01/2016 13:03 10 10/01/2016 12:00 10/01/2016 12:01

EXPECTED VALUE 1 (I need plot in bar chart 😞

AXIS X                           AXIS Y

 Shift 1 (>6:00 to 14:00) 3 Shift 1 (>14:00 to 22:00) 5 Shift 1 (>22:00 to 6:00) 2

EXPECTED VALUE 2 (Only show the duration):

 AVG 00:33:30

Thanks for response that, really im scared!

Best regards.

2 Replies
MVP

Script:

Table:

CREATED,

FINISHED,

Interval(FINISHED - CREATED, 'hh:mm:ss') as Duration

FROM

(html, codepage is 1252, embedded labels, table is @1);

Avg Duration Expression:

=Interval(Avg(Duration), 'hh:mm:ss')

MVP

Here is the other requirement as well:

Table:

CREATED,

FINISHED,

'Shift' & If(Time(Frac(CREATED)) > MakeTime(22) or Time(Frac(CREATED)) <= MakeTime(6), 1,

If(Time(Frac(CREATED)) > MakeTime(14), 2,

If(Time(Frac(CREATED)) > MakeTime(6), 3))) as Shift,

Interval(FINISHED - CREATED, 'hh:mm:ss') as Duration

FROM