Set Analysis - Avg of dates subtraction

Wed, 10 Jul 2013 07:27:03 GMT

Hi,

I need to create a set analysis which retrun the following:

I have 2 tables:

The first is employees: each employee is related to one director and has a date in which he joined the company

employee | director | date_joined

emp_1 | dir_1 | 01/02/2013

emp_2 | dir_3 | 01/03/2013

emp_3 | dir_1 | 02/04/2006

Teh second is calls: each employee can make few calls and each call has a date and a status:

call | employee | call_date | call_status

call_1 emp_1 01/05/2013 ok

call_2 emp_1 06/05/2013 ok

call_3 emp_1 03/05/2013 failed

call_4 emp_3 01/06/2013 ok

I would like to have for each director the average for all his employees of the substraction of employee date joined and the first call with status ok.

I have tried the following, which doesn't work:

aggr (avg (aggr (min(call_date <call_status= ok>), employee) - date_joined),director).

aggr (min(call_date <call_status= ok>), employee) - to have for each employee the first call with status = ok.

Now, I need to calculate the following substraction :employee date_joined - employee first call ( calculated in the previous line)

aggr (min(call_date <call_status= ok>), employee) - date_joined (??)

And to have the average pf this substraction( number of days) per director:

aggr (avg (aggr (min(call_date <call_status= ok>), employee) - date_joined),director).

I would really aprreciate your help.

Re: Set Analysis - Avg of dates subtraction

Gysbert_Wassenaar — Wed, 10 Jul 2013 09:41:08 GMT

If I understand you correctly this expression is what you need: avg(aggr(min({<call_status={'ok'}>}call_date),employee) - date_joined). See attached qvw.

topic Set Analysis - Avg of dates subtraction in QlikView

Set Analysis - Avg of dates subtraction

Re: Set Analysis - Avg of dates subtraction