Average of time difference between two dates of 2 differents rows

[poluvidyasagar]

Hi All,

I am new to Qlikview.

I am trying to achieve this in Qlikview. Can any suggest me how to do it.

My Data Set:

Part	Event	Time
12345	StartTime	4/26/18 10:30 AM
12345	EndTime	4/26/18 12:30 PM
11111	StartTime	4/25/18 10:00 AM
11111	EndTime	4/25/18 11:00 AM
22222	StartTime	4/24/18 11:20 AM
22222	EndTime	4/24/18 12:20 PM

I would like to calculate the difference between start time and end time for each part and average it out. I want this happen in one expression.

In this case the result is (2 hours for first part+1 hour for second part + 1 hour for third part). so the average is 4/3

I have used Only({<Event={'EndTime'}>}Time)-Only({<Event={'StartTime'}>}Time) to get the difference. I am not sure how to calculate the average though.

Can someone help me with this?

Thanks,

[sunny_talwar]

May be this

Avg(Aggr(Only({<Event={'EndTime'}>}Time)-Only({<Event={'StartTime'}>}Time), Part))

[poluvidyasagar]

Thanks Sunny.  It definitely.helped.

To add to my previous question, how do i exclude weekends when i calculate the time difference.

For example:  In the below example, my start time is on Friday and End Time is on Monday. If I exclude weekends, time difference is 10 hours. But based on previous logic, it is considering weekends and giving me a bigger value.

Part	Event	Time
12345	StartTime	4/23/18 10:30 PM
12345	EndTime	4/26/18 08:30 AM

Can you help me on this one too!

Thanks,

[vishsaggi]

may be this extending Sunny's expression try like

Sorry this does not work.

Avg(Aggr( Networkdays(Only({<Event={'EndTime'}>}Time)-Only({<Event={'StartTime'}>}Time)), Part))

[vishsaggi]

Are your start and end times are correct coz 4/23 and 4/26 are not Friday or Monday?

[poluvidyasagar]

Hi Vishwarath,

Sorry. I put the wrong dates there.

Here is updated data:

Part	Event	Time
12345	StartTime	4/20/18 10:30 PM
12345	EndTime	4/23/18 08:30 AM

Thanks,

[sunny_talwar]

Are you looking for difference between the two dates in time for working days?

[poluvidyasagar]

Yes

[vishsaggi]

That is what I understood from his earlier reply. But here we should know how many hours he is considering in a day like 8 hrs or 24 hrs right?

[sunny_talwar]

Yes