topic Re: Help with aggr expression in QlikView

Help with aggr expression

Wed, 25 Nov 2020 16:16:04 GMT

I am new to QW and so trying out few of the things. I have attached the image of the input file that i have used.

I am inserting the above values in straight table. If i write the below expression

=aggr(total,region)

i am getting the below result as, i.e. only 2 records.

But if i modified the result as

=aggr(total,country)

It is giving me the below result. I have not specified to restrict or not allow any such thing.

Can you guys help me understand this behavior of QW. I am using Personal edition of QW.

Re: Help with aggr expression

johnw — Wed, 19 Oct 2016 16:27:12 GMT

An aggr() is building a temporary table in memory. Your aggr(total,region) is like a table with a dimension of region and an expression of total. But total has more than one value for region C, for instance, both 2 and 34. So what do you want to see? You haven't told QlikView what to do in this case. Did you want the sum? The average? The minimum? It has no idea. So it will ONLY show you a total where there is only a single value of total for the region. You need to explicitly tell QlikView what you want to do when there are multiple values. I would assume you wanted the sum of the values. That would be this:

aggr(sum(total),region)

Though I don't think that will do what you really want. It looks like all you're attempting to build is a plain old table with dimensions of region and country, and the sum of total.

Dimension 1 = region
Dimension 2 = country
Expression = sum(total)

No aggr() needed for that.

Re: Help with aggr expression

Wed, 19 Oct 2016 17:37:52 GMT

Thanks John for writing such beautiful reply.

Help me understood AGGR() even better.