4 Replies Latest reply: Sep 22, 2012 4:46 PM by stephane jeanneteau RSS

    script for sum of fields in special order and rules

      Hello,

       

      I need some help ...

       

      I've got a table, with le list of the statuts and date for ID.

      the same ID can have différents statuts at différents date.

      I have also the end date and the duration of each statut.

       

      But now I would like to calculate the sum of the duration of each ID, with theses specifics rules :

      • from the first date I'm going in the ascending time, I add the duration of the statuts 'new', 'affected', 'Reopen'. I do not add the duration of the statut 'question to customer'.
      • AND I STOP the sum when the statut is Closed or Accepted , even if there are other statut 'new', 'affected', 'Reopen' in the dates after this one
      • for the two ID of the joined example, the duration is the sum of the yellow lines, the white line is not added, and the count stop to the blue line. The green line are not in the sum because they are after the blue line.

       

      ScreenHunter_28 Sep. 21 21.32.gif

       

       

      Thank you

       

      stephane