topic Aggr function with ALL qualifier in QlikView

Aggr function with ALL qualifier

Sun, 06 Sep 2015 10:57:25 GMT

Hello experts,

I've been asked to create a straight table chart showing all customers with total recurring revenue more than 100K grouped by father (I have predefined relationships between a father and its children).

So, I've created a calculated dimension using Aggr function and suppressed NULL values:

=If(Aggr(Sum({<Year={"<=$(=GetFieldSelections(Year))"}>} Amount),FatherID)>=100,FatherID)

While the expression in the table is:

Sum({<Year={"<=$(=GetFieldSelections(Year))"}>} Amount)

Attached, are QV and sample excel data files.

Now, I wanted to show the total number of customers in the table's title, so I've created the following expression:

Count(If(Aggr(Sum({<Year={"<=$(=GetFieldSelections(Year))"}>} Amount),FatherID)>=100,FatherID))

Accordingly to the sample data there are 3 customers that match this criteria, but the expression resulted in 2 instead of 3!

Only after adding ALL qualifier it showed the right answer:

Count( ALL If(Aggr(Sum({<Year={"<=$(=GetFieldSelections(Year))"}>} Amount),FatherID)>=100,FatherID))
I'm trying to understand WHY adding the qualifier solved the problem (because for now it is just a happy coincidence for me).

Any ideas that can shed some light on this would be much appreciated!

Thanks,

Anastasya

Re: Aggr function with ALL qualifier

petter — Sun, 06 Sep 2015 11:20:24 GMT

The ALL qualifier is not recommended to use anymore as the combination of the Set Expression identifier 1 and the qualifier TOTAL taken together achieves the same as ALL. Qlik has deprecated the ALL qualifier meaning that you can still use it but it might disappear in the future - then all use of ALL has to be replaced by 1 and TOTAL.

Sum( {1} TOTAL Sales)

is the same as:

SUM( ALL Sales)

So the TOTAL and 1 gives you more flexibility and also covering the ALL case. Furthermore when using ALL you can not use a Set Expression.

Here is a snipping from the Help File of QlikView Desktop:

In previous QlikView versions, the all qualifier may occur before an expression. This is equivalent to using {1} total. In such a case the calculation will be made over all the values of the field in the document, disregarding the chart dimensions and current selections. (The same value is always returned regardless of the logical state in the document.) If the all qualifier is used, a set expression cannot be used, since the all qualifier defines a set by itself. For legacy reasons, the all qualifier will still work in this QlikView version, but may be removed in coming versions.

Re: Aggr function with ALL qualifier

lironbaram — Sun, 06 Sep 2015 11:31:34 GMT

this function will work

='Total: ' & Count({<Year={"<=$(=GetFieldSelections(Year))"}>}Aggr(if(Sum({<Year={"<=$(=GetFieldSelections(Year))"}>} Amount)>=100,only({<Year={"<=$(=GetFieldSelections(Year))"}>}FatherID)),FatherID))

the problem is that the FatherID 222 isn't in the selected data

this why i used the only function

Re: Aggr function with ALL qualifier

Mon, 07 Sep 2015 06:34:06 GMT

Hi Petter,

Thank you for bringing this to my attention!

Your suggested solution works very good!

Anastasya

Re: Aggr function with ALL qualifier

Mon, 07 Sep 2015 06:35:14 GMT

Hi Liron,

Thank you for your help!

This works perfectly and I finally was able to understand why

Anastasya