1 Reply Latest reply: Jul 10, 2013 5:41 AM by Gysbert Wassenaar RSS

    Set Analysis - Avg of dates subtraction



      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.