Distinct count with case when for throughput times

Hi,

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?

Hi, FYI this is how I solved the problem described above:

COUNT (DISTINCT CASE WHEN CALC_THROUGHPUT(CASE_START TO CASE_END, REMAP_TIMESTAMPS(“Activities.csv”.“TimeStamp”, DAYS)) > 21 THEN “Activities.csv”.“FileID” ELSE NULL END) / COUNT_TABLE(“Cases.csv”)

1 Like

Hi @sgizm,

great that you were able to solve the issue by yourself! Even better that you shared your knowledge with everyone! Thank you very much!

If you have any further questions please feel free to post them again in our community!

Best regards,

Justin