The sortable Aggr function in Qlik Sense

Sonja_Bauernfeind — Tue, 10 May 2022 19:23:50 GMT

The Aggr function is used for advanced aggregations, in which the Aggr function is enclosed in another aggregation function, using the array of results from the Aggr function as input to the aggregation in which it is nested. Aggr() returns an array of values for the expression calculated over the stated dimension or dimensions. But the question is if there is way of influencing the order in which the dimension or dimensions are taken into account by the Aggr function.

We see better what I mean with an example:

Let load the following script code to a Qlik Sense app.

load * inline [ Field_1, Field_2, 1,2 1,3 2,15 2,7];

If we now in the front end put the following expression=only(aggr(above(Sum(Field_2)), Field_1))on a text box object, the result will be 5, since the Aggr function will take into account first the value 1 and sen the value 2, but is there any way that the Aggr function takes into account first the value 2 and then the value 1, so the result will be 22 instead.

The Aggr function has been modified in QlikSense comapred to QlikView 11. The syntax of the function in Qlik Sense is: Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter}).

For a detailed explanation of each parameter, please visit help.qlik.com.

Resolution:

The solution is to use the parameter StructuredParameter for the Aggr function. If you use this parameter and modify the expression in the text object to =only(aggr(above(Sum(Field_2)), (Field_1,(Numeric, DESCENDING)))) then you get the desired result.

article The sortable Aggr function in Qlik Sense in Official Support Articles

The sortable Aggr function in Qlik Sense

Resolution: