4 Replies Latest reply on Nov 29, 2015 1:15 PM by Philipp Güth

    Classify average of duration per group

    Philipp Güth

      Hi,

       

      I have processes that have a start date and an end date as well as a priority.

      If the average duration per month of a priority class of processes exceeds a certain threshold I want to categorize them as "Bad".

      The challenge I face is that processes have different priorities AND for each priority there are different thresholds.

      The goal is to have a heatmap with Priority on the y-axis and month on the x-axis.

       

      What I did is to create a calculated field as follows:

      IF [Priority] = 'Prio1' AND [Duration] <= 1 THEN 'Good'

      ELSEIF [Priority] = 'Prio1' AND [Duration] > 1   THEN 'Bad'

      ELSEIF  [Priority] = 'Prio2' AND[Duration]] <= 2 THEN 'Good'

      ELSEIF [Priority] = 'Prio2' AND [Duration] > 2 THEN 'Bad'

      ELSEIF  [Priority] = 'Prio3' AND [Duration] <= 3 THEN 'Good'

      ELSEIF [Priority] = 'Prio3' AND [Duration]  > 3 THEN 'Bad'

      END

       

      However, this seems to divide the processes into "Good" and "Bad" first and then averages the duration. What is required is to average the duration over a class and then check if the result is below a certain threshold. I suspect a LoD calc could resolve this but I wasn't successful with that yet.

       

      Data Sample:

       

      ProcessID

      Priority

      StartTime

      EndTime

      1

      Prio1

      01/03/2015

      03/03/2015

      2

      Prio1

      03/03/2015

      03/03/2015

      3

      Prio2

      05/03/2015

      05/03/2015

      4

      Prio2

      04/03/2015

      07/03/2015

      5

      Prio3

      02/03/2015

      03/03/2015

      6

      Prio3

      01/03/2015

      07/03/2015

       

      Data Sample in plain text:

      ProcessID Priority StartTime EndTime

      1 Prio1 01/03/2015 03/03/2015

      2 Prio1 03/03/2015 03/03/2015

      3 Prio2 05/03/2015 05/03/2015

      4 Prio2 04/03/2015 07/03/2015

      5 Prio3 02/03/2015 03/03/2015

      6 Prio3 01/03/2015 07/03/2015

       

      For confidentiality reasons I cannot share the workbook.

       

      Any help is much appreciated,

      Philipp