Calculate the time Avg

[vinod22kv]

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.

[m_woolf]

Why not calculate the Time Diff in script:

rangesum(-Received,Replied) as TimeDiff

Then you can use the expression: avg(TimeDiff)

[Anonymous · Accepted solution]

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.

[MarcoWedel]

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

[vinod22kv]

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.

[vinod22kv]

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

[MarcoWedel]

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