Hello all,
I am struggling to calculate the average count of activities for similar cases. The calculation returns a wrong result. It doesn’t matter if I use PU_COUNT on the activity table or CALC_REWORK.
Example Data
| Case ID | Parameter | Number of Activities in Activity table |
|---|---|---|
| 1 | A | 10 |
| 2 | A | 10 |
| 3 | A | 20 |
| 4 | B | 15 |
I would like to know the amount (count) of process steps (activities) on average for cases with a certain parameter.
Expected Result
| Parameter | Average activities count |
|---|---|
| A | 13.33 ((10+10+20)/3) |
| B | 15 (15/1) |
What I get
Dimension: Parameter
KPI: AVG(CALC_REWORK())
| Parameter | Average activities |
|---|---|
| A | 15 |
| B | 15 |
It does consider the ‘10’ activities for case 1 and 2 only once. So if the number of activities is the same for multiple cases (which might very well be the case), the calculation is wrong.
If I remove the Dimension, I get the correct overall result of 13.75. But as soon as I add the Parameter as a dimension (which I need), the result is not correct anymore.
Using AVG(PU_COUNT(...)) on the activities table leads to the exact same result.
Can anyone give me a hint on how to get the correct result? My only workaround would be to do some pre-calculations in the transformations...but that shouldn’t be the solution.
Many thanks!