topic Re: Conditional Count and group by in QlikView

Conditional Count and group by

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good day All,

I have a question I created an expression to count the event activity for list of servers
I'd like to return servers where number of events for each server is greater than 150 event per week, but I'm not sure of the correct syntax.

I figured out the count and condition although I'm not sure where I should place the AGGR function or if there another way to have this!

=COUNT({<SERVER = {"=(COUNT(SERVER )>150"}>}SERVER )

if it was a table on DB it will be like this

SELECT WEEK,SERVER,count(SERVER) TOTAL

FROM TABLE

GROUP BY 1,2

HAVING count(SERVER) >150

Thanks in advance

Re: Conditional Count and group by

sunny_talwar — Fri, 20 Apr 2018 14:22:04 GMT

May be this

Sum(Aggr(If(Count(SERVER) > 150, Count(SERVER)), WEEK, SERVER))

Re: Conditional Count and group by

sasiparupudi1 — Fri, 20 Apr 2018 14:55:07 GMT

May be COUNT({<SERVER = {"=Aggr(COUNT(SERVER ),WEEK)>150"}>}SERVER )

Re: Conditional Count and group by

Anonymous — Fri, 20 Apr 2018 15:23:12 GMT

Thanks Sunny
This good, although as we used IF it will return all the servers and for those servers which less than 150 event it will be 0.

I need to display only the servers with more than 150 not all!

Re: Conditional Count and group by

Anonymous — Fri, 20 Apr 2018 15:27:35 GMT

Thanks Sasidhar,

This proposal will not show the events per each server it will display the servers with more than 150 event although the total # of event will displayed as 0 and will display the sum of servers

Thanks in advance

Re: Conditional Count and group by

sunny_talwar — Fri, 20 Apr 2018 15:49:47 GMT

Sorry, I am confused and now sure I understand your concern?