Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rupaliqlik
Creator
Creator

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

5 Replies
Frank_Hartmann
Master II
Master II

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
Creator
Creator
Author

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
Creator
Creator
Author

Hi ,

          stalwar1  Can you help me to get the expected result.

Thanks in advance,

Rupali

kartikaysingh
Contributor III
Contributor III

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.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!