Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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')

Highlighted

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);