topic Re: aggr function performance problem in QlikView

aggr function performance problem

Mon, 26 Jan 2026 18:19:17 GMT

Hi,

I am hitting a strange performance issue with the app I am working on and can not figure out why qlikview is performing the way it does. I have attached a sample application with a slice of my data. I have only included one expression in the sample app (the one with the issue).

Things start to get slow when I reload about 3M rows. By the time I load more data (~5M rows), the app just hangs on the chart (displaying the progress bar on the chart) while calculating the following expression

=count(aggr(memb_id,memb_id, service_date_id, service_type))

when the app freezes, windows stops responding too and the only way out is a hard reboot. I am running this on a laptop with 8gig of ram, however, when i upload the app to our server (96gig memory) same problem is observed.

Please take a look and maybe someone can spot an issue. Does the aggr function just rolls over for larger data sets? I have used the aggr function in other apps but never have seen this qlikview perform like this.

Re: aggr function performance problem

MK_QSL — Sat, 28 Feb 2015 07:32:46 GMT

Change the expression as below

=SUM(Aggr(COUNT(memb_id),service_date_id,service_type))

Re: aggr function performance problem

terezagr — Sat, 28 Feb 2015 09:01:36 GMT

Try to change the order of Aggr and Count in your expression like this:

Aggr(Count(memb_id),service_date_id,service_type)

Re: aggr function performance problem

terezagr — Sat, 28 Feb 2015 09:03:05 GMT

More about Aggr function and how to build it is here: Aggr() function

Re: aggr function performance problem

Sat, 28 Feb 2015 21:43:32 GMT

But these two aggr function are not the same.

My expression:

count(aggr(memb_id,memb_id, service_date_id, service_type))

groups the records by the three dims and then provides a count of the groups (that's what I am looking for).

Your expression:

SUM(Aggr(COUNT(memb_id),service_date_id,service_type)

counts number of rows in each group and then gives me the sum of those counts, it essentially gives me the total number of rows in the data set which could be calculated simply by count(memb_id) as you can see in the attached qvw file.

Re: aggr function performance problem

Sat, 28 Feb 2015 21:44:38 GMT

The expression you provided is incorrect and what it produces does not really need an aggr function. Please see my response to the previous poster.

Re: aggr function performance problem

MK_QSL — Sat, 28 Feb 2015 22:39:08 GMT

Dont understand the logic behind keeping same field twoce in aggr function...

Re: aggr function performance problem

rwunderlich — Sat, 28 Feb 2015 23:29:38 GMT

I'm not sure aggr() is the best approach to get this count. If you want the count of groupings, an expression that will probably run much faster is:

=Count(DISTINCT memb_id & service_date_id & service_type)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: aggr function performance problem

Sat, 28 Feb 2015 23:33:59 GMT

my aggr expression is equivalent to this sql:

select count(*) from (select memb_id from sometable group by memb_id, service_date_id, service_type) as a

the inner select is the aggr function I have and the outer select is like the outer count around the aggr function.

Re: aggr function performance problem

rwunderlich — Sun, 01 Mar 2015 00:02:17 GMT

Another way to write this in SQL would be:

select count(DISTINCT memb_id || service_date_id || service_type) from sometable

That is equivalent to the count(DISTINCT...) suggestion I made below.

-Rob

Re: aggr function performance problem

Sun, 01 Mar 2015 23:47:54 GMT

Thanks Rob for your help. Yes, this alternative expression runs much faster. My app does not freeze and it actually completes the calculation now which is a step in the right direction. However, depending on how much data is sliced by the user from the dim filters, it could take up to a minute or longer to complete.

Do you, or any one else on this forum has other ideas I could build on this?

to reiterate again what the chart is suppose to do:

it is suppose to count and return number of groups in the data set expressed by the following expression:

Count(DISTINCT memb_id & service_date_id & service_type)

Re: aggr function performance problem

jagan — Mon, 02 Mar 2015 02:00:29 GMT

Hi,

Try like this in script

Data:

load

autonumberhash256( memb_id & '_' & service_date_id & '_' & service_type) AS Key

memb_id,

service_date_id,

year,

month,

service_type,

amount,

cust_id,

location_type,

age,

cust_type

from

mydata.qvd

(qvd) where service_type = '97001';

KeyCount:

LOAD

DISTINCT Key,

1 AS KeyCount

RESIDENT Data;

Now you can use Sum(KeyCount) as expression in your charts to calculate the key count.

Hope this helps you.

Regards,

Jagan.

Re: aggr function performance problem

Mon, 02 Mar 2015 04:51:33 GMT

Very clever solution. The worst calculation now takes less than 5 seconds which is acceptable.

Thanks for your reply.