topic Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview. in QlikView

how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

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

Hi Experts,

I want to calculate the average time of last successful 7 times reload of qvd. If some qvds are scheduled on daily basis so I can get last 7 days qvd reload time,if some qvds are refresh on weekly basis so I should get last 7 weekly reload time,and if some qvd's refresh more than 1 time in a single day then I should get that day entries.I'm successful to get those entries.

But My average time is not coming correct.I want to predict that In future at this time in a day qvd should reload that is why I want to get average time or approximate time.

Audit_QVD	End	END_TimeStamp
Audit_Materials_Data.qvd	10/1/2018 10:20:01 PM	10:20:01 PM
Audit_Materials_Data.qvd	10/2/2018 10:20:00 PM	10:20:00 PM
Audit_Materials_Data.qvd	10/3/2018 10:20:11 PM	10:20:11 PM
Audit_Materials_Data.qvd	10/4/2018 10:08:35 AM	10:08:35 AM
Audit_Materials_Data.qvd	10/4/2018 3:14:57 PM	3:14:57 PM
Audit_Materials_Data.qvd	10/4/2018 4:29:07 PM	4:29:07 PM
Audit_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*

Audit_QVD	End	END_TimeStamp
Audit_Sales_Data.qvd	9/26/2018 11:02:20 AM	11:02:20 AM
Audit_Sales_Data.qvd	9/27/2018 11:02:51 AM	11:02:51 AM
Audit_Sales_Data.qvd	9/28/2018 11:02:08 AM	11:02:08 AM
Audit_Sales_Data.qvd	10/1/2018 11:01:45 AM	11:01:45 AM
Audit_Sales_Data.qvd	10/2/2018 11:01:42 AM	11:01:42 AM
Audit_Sales_Data.qvd	10/3/2018 11:02:33 AM	11:02:33 AM
Audit_Sales_Data.qvd	10/4/2018 11:03:53 AM	11:03:53 AM
	What I expected	11:02:27 AM

I have build below formula but It failed to calculate the average for 1st example where I have 4 entries on same day.And If dates are missing in between days then also it gave correct time.(for that I have used interval.)

interval(AVG(Time(Frac(TimeStamp#(End,'MM/DD/YYYY hh:mm:ss TT'))))) AS AverageTime_To_Load

Below is my code:

Last7Time:

load distinct

QVD ,

End,

Time(End) as END_TimeStamp,

Rows,

AutoNumber(End, QVD) as Check

Resident Incremental where AutoNumber(End, QVD)<=7 Order By QVD, End desc;

drop table Incremental;

Final:

load QVD,

interval(AVG(Time(Frac(TimeStamp#(End,'MM/DD/YYYY hh:mm:ss TT'))))) AS AverageTime_To_Load

Resident Last7Time group by QVD;

drop Table Last7Time;

Please help me on this as it is very urgent issue.

Thanks ,

Rupali

kaushik.solanki‌ stalwar1 @Frank Hartmann

Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

Frank_Hartmann — Thu, 11 Oct 2018 13:17:41 GMT

why are you expecting 10:10:25 PM? for me 18:44:41 PM sounds reasonable!

QV uses 24h format and when applying these format to your timestampdates

then 18:44:41 PM is the correct avg time!

Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

rupaliqlik — Thu, 11 Oct 2018 13:41:42 GMT

hi Francs,

I want to indicate aggregate time when qvd will refresh in future.That is why i need that.When i apply the formula in excel it shows correct.

Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

rupaliqlik — Thu, 11 Oct 2018 15:39:38 GMT

Hi ,

stalwar1 Can you help me to get the expected result.

Thanks in advance,

Rupali

Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

kartikaysingh — Fri, 12 Oct 2018 07:14:57 GMT

Hi Rupali,

Can you please let me know how we can calculate Qlikview reload time.

I am new to qlikview so keen on knowing this.

Re: how to calculate average time for when dates are missing or multiple entries on same date in range in qlikview.

kaushiknsolanki — Sat, 13 Oct 2018 07:22:39 GMT

Hi,

I checked and normal Avg function works properly.

I tried Below code and it gives expected result for second data set.

I suspect you there is something wrong in your data (Meaning the data you are checking in excel and in Qlik is different).

DATA:

Load *,num(ENDTIME)- Floor(ENDTIME) as ENDTIMENUM;

Load *,Time(Timestamp#(End,'DD/MM/YYYY hh:mm:ss TT')) as ENDTIME inline [

Audit_QVD, End

Audit_Materials_Data.qvd, 10/1/2018 10:20:01 PM

Audit_Materials_Data.qvd, 10/2/2018 10:20:00 PM

Audit_Materials_Data.qvd, 10/3/2018 10:20:11 PM

Audit_Materials_Data.qvd, 10/4/2018 10:08:35 AM

Audit_Materials_Data.qvd, 10/4/2018 03:14:57 PM

Audit_Materials_Data.qvd, 10/4/2018 04:29:07 PM

Audit_Materials_Data.qvd, 10/4/2018 10:20:02 PM

Audit_Sales_Data.qvd, 9/26/2018 11:02:20 AM

Audit_Sales_Data.qvd, 9/27/2018 11:02:51 AM

Audit_Sales_Data.qvd, 9/28/2018 11:02:08 AM

Audit_Sales_Data.qvd, 10/1/2018 11:01:45 AM

Audit_Sales_Data.qvd, 10/2/2018 11:01:42 AM

Audit_Sales_Data.qvd, 10/3/2018 11:02:33 AM

Audit_Sales_Data.qvd, 10/4/2018 11:03:53 AM

];

AVG:

Load Audit_QVD,Time(Avg(ENDTIMENUM)) as AVGTIME Resident DATA Group by Audit_QVD;

Regards,

Kaushik Solanki