How to get time interval from my records and average them?
Hi,
I would like to do a report that calculate how efficient the person in keying in the entries into the excel worksheet. I have a date column in the excel worksheet that automatically populate the date and time when the person create a new entry (row).
How can i get the last record's time and the first record's time based on a given time interval (8am-10am, 10am-12pm,12pm-2pm,etc) , subtract them and divided by the count of records that falls in the same given time interval?
For example, if Selected Date = 12/01/10 and Time = 10am-12pm. Data is as follows:
11/01/10 10:10
12/01/10 10:43
12/01/10 11:00
12/01/10 11:37
12/01/10 12:24
Average Processing Time = 11:00 - 10:43 (43mins) + 11:37 -11:00 (37mins) / 2
Can anyone provide me with some guidance? I have attached my data file for your reference.
as QlikView does not seem to have a 'datediff' function and adding/substracting date values gives uncontrollable results, consider the attached qvw. Using a custom function in the module that evaluates the datetime values and returns the difference in the passed interval we get what you seem to want. By creating a timeslot table and modifying the date in the table to reflect the starting hour, we can select all entries that have a same starting hour as the selected time slot. Of course this can be tuned and altered, hope it will help you on the way....
The link and calculation between the timeslots are allready in lace, just look at the script and you will notice. I have made the vbscript function such that you can define the 'interval': f.i. hours, minutes, weeks, years, refer to the vbscript reference(s) on the internet to find out the correct naming and working of the interval you are looking for. In the load script the vbfunction is referenced to calculate the difference between the two datetime values. Make sure to sort the load so you will get the right results with the previous() function.
Please refer to atached QVW. As they say, 'there are many roads that lead to Rome', this is just one of them. I am very busy at the moment, but I could fix up this example with your data. Hope this will get you at least on the way.....
I am thankful that you are taking some time out of your busy schedule to assist me. I have done up the report with one last issue which i hope i can get your guidance on. i have attached my qlikview report for your reference.
With the selection of the "TimeSlot", the report should compute the time difference between the records within that time frame. The problem is that the last record that falls within the time slot is actually calculating time difference with the next record that is outside the selection time slot giving a skewed computation.
How can i stop the last record that is within the "Timeslot" from performing calculation ?
You can see from my initial selection that record S/No 14254 is showing processing time of 39.23 which is supposed to have value of null.
Thanks for your kind assistance. I can wait patiently till you are available to guide me on the last issue.
Sorry, I did not get a notification about this post, and I only now look at it again. Why do you sort the records descending in your load? I think an ascending load is more logical, as you are comparing the times against the previous record..... Other than that I cannot see why the last and previous record have a time difference of 1 hour and something..... Did you have a look at the qlikview function 'interval' by the way, this might give more predictable /controlable results then the method you are using now.....
