topic Re: Sum and count in same expression in QlikView

Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 06:29:21 GMT

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

Re: Sum and count in same expression

zhadrakas — Fri, 30 Sep 2016 06:33:53 GMT

try like

SUM(aggr(count(if(log_id_pk > 5,log_tun_id)),log_tun_id))

Re: Sum and count in same expression

swuehl — Fri, 30 Sep 2016 06:35:23 GMT

You need to use advanced aggregation if you want to embed a count in a another aggregation function.

But maybe this is sufficient?

=Sum( log_id_pk >5 ) *-1

Or using advanced aggrgegation:

Sum( Aggr( Sum(If( Log_id_pk >5,1)), log_tun_id))

Re: Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 06:40:46 GMT

Tim,

Thanks for the quick response. Our QV server is down at the moment and i will try as soon as it is up.

Why do we have to use the aggregate function? it could not work with simple sum(if(count condition),1,0)?

Also can you briefly explain the aggr function as i get confused whether to use it inside the count or outside.

Thanks.

Re: Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 06:43:56 GMT

Dear Stefan,

Thanks for your quick response. Our server is down at the moment and i will enter your expression as soon as it is up.

=Sum( log_id_pk >5 ) *-1

- Actually we have Raw Transaction Table so we cannot aggregate the counts as we have to use them for other expressions.

- What is the purpose of *-1? after summation?

Thanks.

Re: Sum and count in same expression

swuehl — Fri, 30 Sep 2016 06:48:57 GMT

=Sum( log_id_pk >5 ) *-1

- Actually we have Raw Transaction Table so we cannot aggregate the counts as we have to use them for other expressions.

- What is the purpose of *-1? after summation?

Could you elaborate on your first statement? How does your data model look like? Could you post some sample records?

Referring to the second question: when executing log_id_pk >5 on row level, a boolean value is returned TRUE or FALSE, or in numeric representation -1 or 0. Summing up the TRUE values returns a negative value, hence you would need to multiply with -1 (or use fabs() ) to get your positive value equal to a count of rows where log_id_pk >5

Re: Sum and count in same expression

zhadrakas — Fri, 30 Sep 2016 06:51:09 GMT

you cant use two aggregation functions like Count and sum in one Expression.

There is were the aggr() Comes into Play. the aggr function created a temporary table of Content.

in this example aggr(count(if(log_id_pk > 5,log_tun_id)),log_tun_id) creates a table like this

Log_tun_id Count(if(log_id_pk>5, log_tun_id))

1 12

2 14

3 16

4 17

Now we want to get the sum of this, so we surround the Expression above with sum().

-> sum( aggr(count(if(log_id_pk > 5,log_tun_id)) )

hope this helps

Re: Sum and count in same expression

Anonymous — Fri, 30 Sep 2016 06:58:25 GMT

Hi Syed:

Sum({<log_id_pk = {"=Count(log_id_pk)>5"}>} 1)

Regards!

Re: Sum and count in same expression

jagan — Fri, 30 Sep 2016 07:05:44 GMT

Hi,

Try like this

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

Sum({<log_id_pk = {"=Count(log_id_pk)>5"}>} DISITNCT log_id_pk)

Hope this helps you.

Regars,

Jagan.

Re: Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 07:55:24 GMT

I tried your suggestion but it is not working. It is giving all zeros.

I tried to switch the count and the if - sum( aggr(if(count(log_id_pk > 5),log_tun_id)) )

But it is not working properly.

Re: Sum and count in same expression

zhadrakas — Fri, 30 Sep 2016 08:01:06 GMT

please try

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

Re: Sum and count in same expression

Anil_Babu_Samineni — Fri, 30 Sep 2016 08:02:14 GMT

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

Re: Sum and count in same expression

rajpreeths — Fri, 30 Sep 2016 09:28:49 GMT

Hi ,

Try this expression...

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

Re: Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 11:08:32 GMT

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.

Re: Sum and count in same expression

jonathandienst — Fri, 30 Sep 2016 11:30:14 GMT

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.

Re: Sum and count in same expression

sjhussain — Fri, 30 Sep 2016 11:48:21 GMT

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.

Re: Sum and count in same expression

ritaaguiar — Tue, 23 Apr 2019 09:08:32 GMT

@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-in-one/m-p/1571326#M133232

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.