Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

rupaliqlik
New Contributor III

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

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_QVDEndEND_TimeStamp
Audit_Materials_Data.qvd10/1/2018 10:20:01 PM10:20:01 PM
Audit_Materials_Data.qvd10/2/2018 10:20:00 PM10:20:00 PM
Audit_Materials_Data.qvd10/3/2018 10:20:11 PM10:20:11 PM
Audit_Materials_Data.qvd10/4/2018 10:08:35 AM10:08:35 AM
Audit_Materials_Data.qvd10/4/2018 3:14:57 PM3:14:57 PM
Audit_Materials_Data.qvd10/4/2018 4:29:07 PM4:29:07 PM
Audit_Materials_Data.qvd10/4/2018 10:20:02 PM10:20:02 PM
What I expected10:10:25 PM
I have received                18:44:41 PM

Audit_QVDEndEND_TimeStamp
Audit_Sales_Data.qvd9/26/2018 11:02:20 AM11:02:20 AM
Audit_Sales_Data.qvd9/27/2018 11:02:51 AM11:02:51 AM
Audit_Sales_Data.qvd9/28/2018 11:02:08 AM11:02:08 AM
Audit_Sales_Data.qvd10/1/2018 11:01:45 AM11:01:45 AM
Audit_Sales_Data.qvd10/2/2018 11:01:42 AM11:01:42 AM
Audit_Sales_Data.qvd10/3/2018 11:02:33 AM11:02:33 AM
Audit_Sales_Data.qvd10/4/2018 11:03:53 AM11: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

Tags (1)
5 Replies
Frank_Hartmann
Honored Contributor

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

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!




rupaliqlik
New Contributor III

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

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.

rupaliqlik
New Contributor III

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

Hi ,

          stalwar1  Can you help me to get the expected result.

Thanks in advance,

Rupali

kartikaysingh
New Contributor II

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

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.

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

Community Browser