I am trying to count the distinct cases from my activity table, where a condition is satisfied. The condition is that the average throughput time should more above a certain number. Ultimately I want to know the percent of that satisfied condition divided by all cases (e.g., 7 percent of cases took more than 21 days to handle). I use the followed pql:
COUNT(DISTINCT CASE WHEN (CALC_THROUGHPUT(CASE_START TO CASE_END, REMAP_TIMESTAMPS(“Activities.csv”.“TimeStamp”, DAYS))) > 21 THEN 1 ELSE NULL END) / COUNT_TABLE(“Cases.csv”)
This works well when I use a single gauge, however, when I try to use the same formula in an OLAP table it fails. In the table I want to have another column with person names who handled those activities (e.g., for Andrew, 7 percent of cases took longer than 21 days to process). This creates a problem because in the activity table a person can handle several activities on a single case. I need to find a way to get the distinct throughput times for each person in an aggregated way for the same cases.
Any recommendations how to solve this?