topic Aggregate and exclude selection in QlikView

Aggregate and exclude selection

Tue, 10 May 2011 14:38:44 GMT

Hi,

I have a problem with excluding the date selection from an aggregate function.

sum({1}aggr(if((Reg_Code = '3' or (Reg_Code = '2' and Type = '4') or Reg_Code = 4)

and Cancelled = '0', today() - Start_date),Reference_Number))

Please could someone assist, as far as I know, {1} should exclude the date selection and any other selection. I've used it before but not combined with Aggr.

Thanks,

Shane

Re: Aggregate and exclude selection

ToniKautto — Tue, 10 May 2011 15:06:01 GMT

It is correct that {1} will run your Sum over all data, eg disregarding all selections.

The way you use Aggr() is not at all suitable as far as I can tell. If you look in the manual (press F1 in desktop client) you will find the definition as below. This means that in the most basic scenario you begin your aggr statement with an expression, followed by the dimensions over where the aggr should be calculated.

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

In your sample you setting the first part in the Aggr() could be Null, since your IF statement has no else clause. Not sure aggregating in this way on a null value will be successfull.

To make further advising easier please attach a sample of your current app loaded with data, and a minimum set of objects. In case the data is sensitive, please scramble the fields to make them unreadable;

Settings > Document Settings > Scrambling

Aggregate and exclude selection

Tue, 10 May 2011 15:09:23 GMT

Why don't you try with Total instead of {1}.