how to calculate average time for time values in mm:ss when dates are mssing in range

[rupaliqlik]

hi,

I want to calculate the average time of qvd reload.If my qvd reload everyday on particular time then I will fetch last 7 days audit log file and load 7 days qvd reload time and find average time when My qvd should refresh today.I will get approximate time.Only prlem is that when anyday extracter is not refresh at that time that day entry will not be included in that audit file.here 22 and 23 date is missing

.that is why I got wrong average time.

QVD	%LoadEnd	LoadTime
qvBPC_ProjectPlanning.qvd	9/19/2018 6:13:02 AM	6:13:02 AM
qvBPC_ProjectPlanning.qvd	9/20/2018 6:13:13 AM	6:13:13 AM
qvBPC_ProjectPlanning.qvd	9/21/2018 6:14:42 AM	6:14:42 AM
qvBPC_ProjectPlanning.qvd	9/24/2018 6:12:56 AM	6:12:56 AM
qvBPC_ProjectPlanning.qvd	9/25/2018 6:18:38 AM	6:18:38 AM

What I got result:1:26:30 AM    What I expected:6:14:10 AM

My code is like that:

load

Distinct

%QVDName as QVD,

Time(Avg(Time(%LoadEnd))) as AverageTime_To_Load

FROM \\dwil0699qvfs01\Qlikview\QV3\QV4\QV5\QVDocs\BI_US\DEV\DataModel\TestRupali\Audit\Audit_qvBPC_ProjectPlanning_Extract.qvd(qvd)

Where Num(Date(floor(%LoadEnd),'DD/MM/YYYY'))>=num(today())-7 group by %QVDName;

Please help me if Im wrong somewhere.

kaushik.solanki‌ stalwar1‌marcus_sommer‌kush141087‌cuv‌bwisealiahmad‌

[Frank_Hartmann]

in script:

Time#(LoadTime,'hh:mm:ss TT') as LoadTime

and textbox after reload:

=Interval(Avg(LoadTime), 'hh:mm:ss TT')

hope this helps

[rupaliqlik]

HI frank ,

               For the same day entries it failed to calculate average time.

Audit_QVD                     End                               END_TimeStamp

Materials_Data.qvd   10/1/2018 10:20:01 PM      10:20:01 PM

Materials_Data.qvd   10/2/2018 10:20:00 PM      10:20:00 PM

Materials_Data.qvd   10/3/2018 10:20:11 PM      10:20:11 PM

Materials_Data.qvd   10/4/2018 10:08:35 AM      10:08:35 AM

Materials_Data.qvd   10/4/2018 3:14:57 PM        3:14:57 PM

Materials_Data.qvd   10/4/2018 4:29:07 PM        4:29:07 PM

Materials_Data.qvd   10/4/2018 10:20:02 PM      10:20:02 PM

What I expected 10:10:25 PM

I have received 18:44:41 PM