Sum and count in same expression

[sjhussain]

Hello

Hope all is well with everyone.

We are trying to do sum and count of in the same expression but are not getting it right.

We have devices  (log_tun_id) and for each device we have number of packets (log_id_pk). What we want to do is for each device (log_tun_id) we would like to show how many times the packets (log_id_pk) is greater than certain value.

We are using the following expression but it is not working.  Seems like Sum and count does not work in the same expression?

Dimension: log_tun_id

Expression: Sum(if(count log_id_pk > 5, 1, 0) )

Will appreciate if someone can assist.

Thanks.

Message was edited by: Syed Jawwad Hussain

Message was edited by: Syed Jawwad Hussain Sorry actually the requirements was quoted wrong to me. The following are the correct requirements. * The count of log_id_pk for each device (tun_id_pk) should be equal to 15 * We want to find out the number of days when the count of log_id_pk for each tun_id_pk > 15 * Selecting the device should give us the list of days and the count where the count has been greater than 15 What i have tried is the following: dimension: log_tun_id expression: sum(aggr(if(count(log_id_pk)>3,1,0), datetimeindata_date, log_tun_id)) And it seems to be working fine. Thanks to all for the assistance as it really helped me to do the expression

[zhadrakas]

please try

Sum(Aggr(if(count(log_id_pk) > 5, 1, 0),  log_id_pk))

[Anil_Babu_Samineni]

Have you tried from Script? Would you be able to provide sample

[rajpreeths]

Hi ,

Try this expression...

sum(aggr(if(count(log_id_pk ])>5,1,0),log_tun_id,log_id_pk ))

[sjhussain]

Tim,

I tried your expression but it is not working.  It is just giving me zeros.

I even changed log_id_pk to tun_id_pk - Sum(Aggr(if(count(log_id_pk) > 5, 1, 0),  tun_id_pk))

Anyother ideas.

Thanks.

[jonathandienst · Accepted solution]

This expression

=Sum(Aggr(if(count(log_id_pk) > 5, 1, 0), tun_id_pk))

is syntactically correct for the information you have supplied. If that is not working, there is some other factor coming into play. If you upload a small sample qvw file together with the results you expect, then we can do more than just make wild guesses at the solution.

[sjhussain]

Jonathan,

I have added the data to the query.  Let me know if you are able to find it.  Also we are looking for count > 1440.

Thanks for the assistance.

[ritaaguiar]

@sjhussain @jonathandienst @rajpreeths @Anil_Babu_Samineni @zhadrakas 

Hello, I have a similar question on this forum:

https://community.qlik.com/t5/New-to-Qlik-Sense/Display-Pie-Chart-with-the-Count-of-some-data-values...

I was wondering how could I Sum the Count of several different values (by specifying which ones I want to Sum) to obtain the total percentage in a pie chart.

Does anyone here know how to help? I would really appreciate it.