(Un)sortable Aggr

[levente81]

Hi there,

I wonder why none of these expressions give back properly sorted result:

Concat(aggr(sum({1} b)
,(a,(Text, Descending)))
,' | ')

Concat(aggr(sum({1} b)
,(a,(=sum({1} b),Descending)))
,' | ')

on this sample data:

temp:
load * Inline [
a, b
a1, 100
a2, 10
a3, 50
];

Is there any learning material that explains what exactly happens in advanced aggr formulas?

Thanks,

Levente

[udit_k]

use sort by expression :-

[levente81]

Thanks, but I need to know what is created in the phantom table when aggr is evaluated - I need to be sure that I can get let's say the Nth value out of it and use it for subsequent calculation steps.

There should be a proper description of this function and also about the way it is supposed to be working.

Official articles tell that Aggr is sortable, and still it is easy to challenge this statement... result of Aggr is something random list that developers can not properly foresee, that is what I learned so far...

Is there anyone, who can explain what happens in Aggr bit by bit? I (and lot of other developers I guess) am eager to understand it to be able to use it for advanced calculations...

[marcus_sommer]

Please elaborate your expectation in more details - especially how should the results look like and why should it happens in this way?

[levente81]

Hi Marcus,

the result should look like sort is working... I do not get what you mean whith this question...

There is a basic sample data. And Aggr does not sort it the way it is described in the documentation.

result of 

Concat(aggr(sum({1} b)
,(a,(Text, Descending)))
,' | ')

should be a temporary table that looks like this:

sorted_temp:
a, b
a3, 50
a2, 10
a1, 100

and the concat() expression shall result "50 | 10 | 100"

while result of 

Concat(aggr(sum({1} b)
,(a,(=sum({1} b),Descending)))
,' | ')

should be a temporary table that looks like this:

sorted_temp:
a, b
a1, 100
a3, 50
a2, 10​

and the concat() expression shall result "100 | 50 | 10"

If this is not the case then please tell me what shall I expect as sorted aggr() results and WHY?

Thanks and best regards,

Levente

[marcus_sommer]

I think there are several issues within your logic. Most important you want to look on an aggr-sorting without using it because as far as you wrapped the aggr() with an outer-aggregation you will loose the aggr-sorting again because the most outer function will determine the result.

Therefore by applying the concat() around the aggr() the concat() will sort alphanumerically the result unless you specify a native and numeric sorting-parameter. This means you couldn't apply an expression as sorting - as well as within the aggr() - only native fields are allowed and in this way described within the help.

In generally it's a complicated matter and I suggest that you tries to solve your task behind this question on another way by generally avoiding the use of aggr() probably by extending your data-model. Even if you get such solution technically to work the needed complexity and the potentially negative performance impact are speaking against such approaches.

Nevertheless if you want to play with this feature you need to apply this within a calculated dimension in a table-chart and enabling an appropriate sorting within the table. Here an example what's meant:

[levente81]

Hi Marcus,

"as far as you wrapped the aggr() with an outer-aggregation you will loose the aggr-sorting again because the most outer function will determine the result."

Well, that answers the enigma... It is a bit sad, that a sorted list is resorted "randomly" without offering any possibility to developer to accept the resorting...

Thanks, at least now I know that I can not do yet in Qlik what I want...

Levente

[marcus_sommer]

Are you really sure that there is no possibility to create the wanted logic within the script? Or at least to do any preparing steps to simplify the needed sorting, maybe with extra sorting-columns and/or using dual() values for the dimensions?