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

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

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

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‌

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

Frank_Hartmann — Tue, 25 Sep 2018 19:35:35 GMT

in script:

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

and textbox after reload:

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

hope this helps

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

rupaliqlik — Thu, 11 Oct 2018 10:27:35 GMT

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