Set Analysis - Avg of dates subtraction

[#1]

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.

[Gysbert_Wassenaar]

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.