Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead 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 IDCREATEDFINISHED
101/01/2016 12:0001/01/2016 12:15
202/01/2016 04:0002/01/2016 05:40
303/01/2016 21:0003/01/2016 21:12
404/01/2016 22:0004/01/2016 22:10
505/01/2016 15:0005/01/2016 15:05
606/01/2016 16:0006/01/2016 16:15
707/01/2016 04:0007/01/2016 06:50
808/01/2016 18:0008/01/2016 18:04
909/01/2016 13:0009/01/2016 13:03
1010/01/2016 12:0010/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
sunny_talwar

Script:

Table:

LOAD [TASK ID],

     CREATED,

     FINISHED,

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

FROM

[https://community.qlik.com/thread/213946]

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

Avg Duration Expression:

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

sunny_talwar

Here is the other requirement as well:

Capture.PNG

Table:

LOAD [TASK ID],

     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

[https://community.qlik.com/thread/213946]

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