Filtering average throughput time in olap table

Hi, I want to filter the invoice handlers whose average throughput time is more than 21 days. The following pql works though looking ugly:

CASE WHEN AVG(CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Activities.csv”.“handlingTimeStamp”, DAYS))) > 21.00 THEN AVG(CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Activities.csv”.“handlingTimeStamp”, DAYS))) ELSE NULL END

However, in the olap table I still have the handler names with NULL processing times (ones with less than 21 days avg). How can I remove those nulls so that my table only has the names and avg throughput time >21 ?

Hi,

you can add a component filter which filters out the null results:
FILTER ISNULL(CASE WHEN AVG(CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Activities.csv”.“handlingTimeStamp”, DAYS))) > 21.00 THEN AVG(CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Activities.csv”.“handlingTimeStamp”, DAYS))) ELSE NULL END) = 0;

Best,
Viana

1 Like

Hi, thanks for the reply, However, it did not work for me as a component filter. It gives this error: “An aggregation is not a function. Please check that there are no dimensions and aggregations used together as function inputs.”

I have Enterprise Server Edition 4.6 if it helps.

BR