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:

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