topic Re: Set Analysis Using Interval(), Avg() and Max() in QlikView

Set Analysis Using Interval(), Avg() and Max()

Wed, 15 Jul 2015 13:22:26 GMT

I am trying to find the average time between the last time open tickets were updated and now.

So far I have this working

=Interval(now()-

DATE(MAX(IF(ReportingDate >=(vStartDate)

AND ReportingDate <= (vEndDate)

AND INCIDENTS_Open_State_Flag=1

AND INCIDENTS_Incident_Flag=1,

ReportingDateTime, 'YYYY-MM-DD, HH:MM:SS'))), 'hh:mm')

What this is doing at the moment is simply selecting the most recent update datetime across all support tickets and working out the difference between that and now.

What I would like to do is calculate the above but grab the most recent update datetime per ticket and then average it.

I've tried and tried but cannot get this to work, any suggestions?

(I'm relatively new to Qlik coding so am totally confused by how to achieve this with set analysis)

Re: Set Analysis Using Interval(), Avg() and Max()

Nicole-Smith — Wed, 15 Jul 2015 16:22:00 GMT

Writing your function using set analysis:

Interval(now()-DATE(MAX({<ReportingDate={'>=$(vStartDate)<=$(vEndDate)'},INCIDENTS_Open_State_Flag={1},INCIDENTS_Incident_Flag={1} ReportingDateTime), 'YYYY-MM-DD, HH:MM:SS'), 'hh:mm')

How to take the average of your function per ticket:

avg(aggr(Interval(now()-DATE(MAX({<ReportingDate={'>=$(vStartDate)<=$(vEndDate)'},INCIDENTS_Open_State_Flag={1},INCIDENTS_Incident_Flag={1} ReportingDateTime), 'YYYY-MM-DD, HH:MM:SS'), 'hh:mm'),TicketID))

Re: Set Analysis Using Interval(), Avg() and Max()

Thu, 16 Jul 2015 07:35:14 GMT

Hi Nicole,

Thank you so much for your response.

If I try and use your avg(aggr function for my headline bubble it simply outputs '-' (doesn't error though).

If I try to to use your one without the average as a column in a table of mine to test the set theory I get 'Error in set modifier expression'.

Any ideas?

Re: Set Analysis Using Interval(), Avg() and Max()

Nicole-Smith — Sun, 19 Jul 2015 01:06:26 GMT

Can you post a sample QVW? I will be able to troubleshoot if I'm able to see what you're working with.

Preparing examples for Upload - Reduction and Data Scrambling