topic Re: Calculate the time Avg in QlikView

Calculate the time Avg

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

Hi,

I have two fields Received and Replied As bellow. Now i need the average of time difference in text object.

Ex:- avg(Replied-Received).

And the origenal data is attached bellow please help me out.

Received	Replied
5/20/2018 8:00	Sun, 20 May 2018 06:59:29 +0000
5/20/2018 9:50	Sun, 20 May 2018 04:48:19 -0400 (EDT)
5/20/2018 10:20	Sun, 20 May 2018 09:17:34 +0000
5/20/2018 10:50	Sun, 20 May 2018 09:48:09 +0000
5/20/2018 11:10	Sun, 20 May 2018 11:08:29 +0100
5/20/2018 12:20	Sun, 20 May 2018 12:17:56 +0100
5/20/2018 14:00	Sun, 20 May 2018 13:55:10 +0100
5/20/2018 14:00	Sun, 20 May 2018 13:56:01 +0100

Thanks and Regards,

Vinod.

Re: Calculate the time Avg

m_woolf — Thu, 14 Jun 2018 16:05:45 GMT

Why not calculate the Time Diff in script:

rangesum(-Received,Replied) as TimeDiff

Then you can use the expression: avg(TimeDiff)

Re: Calculate the time Avg

Anonymous — Thu, 14 Jun 2018 19:07:57 GMT

Hello!

Based on your data, I would do something like this:

Data:

LOAD Received,

Replied,

Received - Date#(Mid(Replied, 6, 20), 'DD MMMM YYYY hh:mm:ss') As Diff

FROM

[Time avg.xlsx]

(ooxml, embedded labels, table is Sheet1);

And create this expression on a text object ...

=Interval(Avg(Diff), 'DD hh:mm:ss')

to get the average difference in days and hours, or ...

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

to get it just in hours, minutes and seconds.

But, be careful! Some of your records are not well formatted, as this one ...

"24 May 2018 12:02:50 +0100" (note the lack of the first 3 letters at the beginning!)

...on the 303th row of your Excel spreadsheet. This will not be taken into consideration on the avarege calculation, as the "Diff" will be null on this case.

Re: Calculate the time Avg

MarcoWedel — Thu, 14 Jun 2018 20:34:52 GMT

Hi,

maybe another solution could be

table1:
LOAD *,
     Interval(Received-Replied,'d hh:mm:ss') as TimeDiff;
LOAD RecNo() as ID,
     Received, 
     Replied as RepliedTxt,
     Timestamp(ConvertToLocalTime(Timestamp#(Mid(Replied,6,20),'DD MMM YYYY hh:mm:ss'),'GMT'&Mid(Replied,FindOneOf(Replied,'+-'),3)&':'&Mid(Replied,FindOneOf(Replied,'+-')+3,2),1),'MM/DD/YYYY hh:mm:ss') as Replied,
     Mid(Replied,FindOneOf(Replied,'+-'),5) as TimeZone
FROM [https://community.qlik.com/servlet/JiveServlet/download/1502299-328732/Time%20avg.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

Re: Calculate the time Avg

vinod22kv — Fri, 15 Jun 2018 11:07:44 GMT

Hi,

Thans a lot for your help.

i have notised time difference between replied and repliedtxt fields, both ar same fields but showing different values. can you please check that one.

Re: Calculate the time Avg

vinod22kv — Fri, 15 Jun 2018 11:51:48 GMT

Thanks a lot for your help.

When i am checking the difference manuvealy it is showing wrong data for an example record no 679 received date is

15 may 2018 13:25:52 and replied date is 26/05/2018 1:25:56 pm. then the difference is showing only 00:00:04. this is wrong this is not considering the days. Can you please help me out??

Thanks and Regards,

Vinod

Re: Calculate the time Avg

MarcoWedel — Fri, 15 Jun 2018 20:30:17 GMT

I tried to apply the time zone information included in your timestamps as well using the ConvertToLocalTime() function.