topic AGGREGATE FUNCTION PROBLEM in QlikView

AGGREGATE FUNCTION PROBLEM

Tue, 20 Dec 2011 07:57:57 GMT

I am in trouble with this expression:

=concat( round(AGGR(SUM(W_FT_VALORE_NETTO),C_PROVINCIA_FATT)), '|',fieldIndex('C_PROVINCIA_FATT', C_PROVINCIA_FATT))

This expression works and it gives me (for example) one result for each "C_PROVINCIA_FATT"

But if I add a condition as below:

=concat( IF( AGGR( SUM( W_FT_VALORE_NETTO),C_PROVINCIA_FATT)>9999,'100','0'), '|',fieldIndex('C_PROVINCIA_FATT', C_PROVINCIA_FATT))

It's going to give not the aggregation but a lot of results as the numbers of C_PROVINCA_FATT ....

Anybody can help me?

orital81 — Tue, 20 Dec 2011 11:38:41 GMT

Your expression shows that you actualy want to see line count as the number of C_PROVINCA_FATT.

Since this is the right part of your Concatenation.

Anyway, If you want to see the distinct row count of C_PROVINCA_FATT

try adding Distinct as shown below:

concat( IF(AGGR(distinct SUM(W_FT_VALORE_NETTO),C_PROVINCIA_FATT)>9999,'100','0'), '|',fieldIndex('C_PROVINCIA_FATT', C_PROVINCIA_FATT))

vgutkovsky — Tue, 20 Dec 2011 15:52:11 GMT

Try this:

concat(distinct

aggr(

if(SUM(W_FT_VALORE_NETTO)>9999,100,0),

C_PROVINCIA_FATT

),'|',fieldIndex('C_PROVINCIA_FATT', C_PROVINCIA_FATT))

I'm not positive this is the exact syntax (a little hard to write this blind), but give it a shot.

Regards,

Vlad