Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

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

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

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