Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
SmartProtection
Contributor II
Contributor II

Calculate average elapsed time

Hi everybody, have been tasked with a KPI that I can't seem to figure out, so we'll see if anyone can give ideas. 

To keep it very simple, my company sends out queries to the internet, and clients hire us to do this for their products and present them with the results we collect. 

Depending on the service level contracted, we have to send out queries at least 1x every 24 hours, 1x every 12 hrs, 1x every 6 hrs, 1x every hr, etc. My boss wants me to calculate a KPI that would be the average time elapsed between each query sent out. From there I could place filters to segment by client, product, service, etc. 

We have a table, queries_executed, that includes a column which registers the time that each query was sent out. Is there a possible way to calculate the time difference for each of the executed queries in chronological order, and then calculate the average of these time differences? 

As an alternative, I have been able to calcule the avg. number of queries sent out per week/day/hr., but my boss is determined to have a timestamp KPI representing the average time difference between executed queries, to ensure that we are meeting the agreed-upon service levels  for each client, which is sending out a query once every x hrs (depending on service level contracted). 

 

Including some of the relevant column headers in the Queries_Executed table: 

 

- query_execution_id (generates 1 unique id for each query that is generated)

- query_executed_time (timestamp)

- query_id (this is the id of the phrase used in the query, which can be executed more than 1 time)

- network 

-client_id

-product_id

-number_results_collected

 

Any feedback or ideas to get me on the right track would be greatly appreciated! Thanks!

 

 

 

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

depending on volume:

1. you can loop through your data (as you said) in chronological order and use the peek function to get the previous time stamp

2. if the volume is large add a rownumber to each record (order by timestamp), then inner join to itself rownumber-1 and the timestamp, this way you have in one record the current rownumber, the current timestamp of that row, the timestamp of prior row (rownumber-1), you can now get the difference/interval

View solution in original post

4 Replies
edwin
Specialist III
Specialist III

depending on volume:

1. you can loop through your data (as you said) in chronological order and use the peek function to get the previous time stamp

2. if the volume is large add a rownumber to each record (order by timestamp), then inner join to itself rownumber-1 and the timestamp, this way you have in one record the current rownumber, the current timestamp of that row, the timestamp of prior row (rownumber-1), you can now get the difference/interval

View solution in original post

edwin
Specialist III
Specialist III

obviously, the first row will drop off (as there will be no row 0 to join with).  if you need that row, do a left join

SmartProtection
Contributor II
Contributor II
Author

Thanks! The peek function worked. I have the flat rate for all overall queries, however it doesn't work when I apply filters (such as client, week, product, etc.) - the equation continues calculating the time difference between the query executed immediately before, regardless of the filters applied. 

I'm happy with having at least the overall flat rate, but if you have any ideas regarding the filters, I'd be happy to hear you out. Thanks!

edwin
Specialist III
Specialist III

both will work but be warned that the solution using peek function is slower and may not be feasible for large volume.  glad it helped and yw.