FILTER with COUNT


#1

Hi everyone,

I’m trying to make a filter based on a count of an attribute of a table. However, I got the error that I can’t filter with an aggregation function. With this in mind, can someone clarify me how can I circumvent the fact that it is not possible to filter based on aggregation functions?

Thank you in advance,
Catarina Amaral


#3

Hi Catarina,

the count is probably related to some kind of filtered information? Can you please provide a specific example so that I can answer your question in more detail.

Thanks and best regards,
Viana


#4

Hi Viana,

Thanks for your reply.

I have my activity table where I have column A and B. What I want to do is know the number of elements of A that have associated more than 1 element of B. In this way, I used the component ‘Number’ with the following formula ‘COUNT (DISTINCT “ACTIVITIES”. “A”)’. To get not only the number of elements of A, but the number of elements of A with more than 1 element of B I thought of filtering the component with the following formula 'FILTER COUNT (DISTINCT “ACTIVITIES”. “B”) > 1 '. However, I got the error that I can not filter with an aggregation function. Can you tell me what I should do to get the result I want?

Thank you in advance for your help.

Best regards,
Catarina Amaral


#5

Hi Catarina,

the best solution for your example is to write your condition directly in the KPI. The PQL code would look like that:

CASE WHEN COUNT(DISTINCT “ACTIVITIES”.“B”) > 1 THEN COUNT(DISTINCT “ACTIVITIES”.“A”) ELSE NULL END

Please let me know if that works for you.

Best Regards,
Viana