Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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.
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.
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