topic Aggr function in QlikView

Aggr function

marco_puccetti — Tue, 16 Jun 2015 14:43:00 GMT

Hello can you tell me how does the aggr function works?

in this case aggr(sum(Sales), Country)

what is generated?

Thanks

Marco

Re: Aggr function

petter — Tue, 16 Jun 2015 14:46:53 GMT

You will get 0, 1 or more values. Each value is grouped by Country and the value is the sum of Sales for that particular country.

That is why you do safest in wrapping your Aggr() in an aggregate function to do something about the multiple values that might be returned.

Re: Aggr function

giakoum — Tue, 16 Jun 2015 14:50:23 GMT

It is like creating a straight table with Country as dimension and sum(Sales) as expression.

That is why many values are returned.

Usually it is combined with some other function like RangeSum, so that QlikView knows which value to show from the many returned.

Re: Aggr function

maxgro — Tue, 16 Jun 2015 14:52:40 GMT

like in SQL

select sum(Sales), Country from ..... group by Country

QlikView help

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a 'local chart', evaluated in the context where the aggr function resides. Each dimension must be a single field. It cannot be an expression (calculated dimension).

If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is preceded by the distinct qualifier or if no qualifier is used at all, each combination of dimension values will generate only one return value.

Re: Aggr function

marco_puccetti — Tue, 16 Jun 2015 15:55:33 GMT

How can i test (to display the list of values) in a text box the structure of Aggr function as you have said?

Thanks

Marco

Re: Aggr function

maxgro — Tue, 16 Jun 2015 15:59:38 GMT

=concat (aggr(sum(Sales), Country) , chr(10))

Re: Aggr function

marco_puccetti — Wed, 17 Jun 2015 07:41:37 GMT

If i use the statement with these datas:

Prodotto Valori

PIPPO 1

PLUTO 2

TOPOLINO 3

What should i get with this statement concat (aggr(Prodotto,sum(Valori)) , chr(10)) ?

If i try i get error: Nested aggregation not allowed,

If i try with the inverted expression (aggr(sum(Valori),Prodotto) , chr(10)) i get only numbers is it correct?

Thanks

Marco

Re: Aggr function

maxgro — Wed, 17 Jun 2015 08:02:24 GMT

expression in textbox is

=concat (aggr(sum(Valori),Prodotto) , chr(10))

chr(10) is the separator (line feed), change with ' ' if you want all the values on the same line

Re: Aggr function

marco_puccetti — Wed, 17 Jun 2015 08:16:29 GMT

So the product name is not showed within the aggr function?

Marco

Re: Aggr function

maxgro — Wed, 17 Jun 2015 08:25:35 GMT

maybe

=concat (aggr(Prodotto & ' ' & sum(Valori),Prodotto) , ' - ')

Re: Aggr function

sasiparupudi1 — Wed, 17 Jun 2015 08:45:05 GMT

aggr(sum(Valori),Prodotto)

the above statement gives a total sum of valorie values per each Prodotto in your table.

I think it will make more sense if had you table data defined like this

LOAD * Inline

[

Prodotto,Valori

PIPPO,1

PIPPO,10

PIPPO,100

PLUTO,2

PLUTO,20

PLUTO,200

TOPOLINO,3

TOPOLINO,30

TOPOLINO,300

];

Re: Aggr function

marco_puccetti — Wed, 17 Jun 2015 08:45:25 GMT

In this case it works.

So in the aggregation is showed only the expression values (first argument).

Thanks

Marco

Re: Aggr function

marco_puccetti — Wed, 17 Jun 2015 09:07:50 GMT

How is it created the Aggragate table?

Thanks

Marco

Re: Aggr function

sasiparupudi1 — Wed, 17 Jun 2015 09:21:59 GMT

Please have a look at the attachement.

Basically selected the dimension Prodotto and an expression columns called aggrgate which has the formula Aggr(Sum(Valori),Prodotto)

Sasi

Re: Aggr function

marco_puccetti — Wed, 17 Jun 2015 09:39:26 GMT

OK you have to use the prodotto as dimension and this statement to get the data aggr(sum(Valori),Prodotto).

Thanks

Marco