5 Replies Latest reply: Oct 13, 2018 3:22 AM by Kaushik Solanki RSS

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

    RUPALI ETHAPE

      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