topic Re: Average of time difference between two dates of 2 differents rows in QlikView

Average of time difference between two dates of 2 differents rows

poluvidyasagar — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Average of time difference between two dates of 2 differents rows

sunny_talwar — Thu, 26 Apr 2018 18:49:40 GMT

May be this

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

Re: Average of time difference between two dates of 2 differents rows

poluvidyasagar — Thu, 26 Apr 2018 19:59:11 GMT

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,

Re: Average of time difference between two dates of 2 differents rows

vishsaggi — Thu, 26 Apr 2018 20:24:33 GMT

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

Re: Average of time difference between two dates of 2 differents rows

vishsaggi — Thu, 26 Apr 2018 20:32:39 GMT

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

Re: Average of time difference between two dates of 2 differents rows

poluvidyasagar — Thu, 26 Apr 2018 20:36:28 GMT

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,

Re: Average of time difference between two dates of 2 differents rows

sunny_talwar — Thu, 26 Apr 2018 21:00:36 GMT

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

Re: Average of time difference between two dates of 2 differents rows

poluvidyasagar — Thu, 26 Apr 2018 21:03:57 GMT

Yes

Re: Average of time difference between two dates of 2 differents rows

vishsaggi — Thu, 26 Apr 2018 21:04:33 GMT

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?

Re: Average of time difference between two dates of 2 differents rows

sunny_talwar — Thu, 26 Apr 2018 21:08:28 GMT

Yes

Re: Average of time difference between two dates of 2 differents rows

poluvidyasagar — Thu, 26 Apr 2018 21:08:33 GMT

I would like to know for both cases.

How to calculate if it is 8 hours a day and 24 hours a day?