The sortable Aggr function is finally here!

[ArturoMuñoz]

Dear Qlik friends & family,

I’m very pleased to announce that one of our very favorite functions has been finally upgraded with a nice and longtime requested functionality. Aggr() virtual table is now sortable!

Note: For those of you not familiar with what Aggr() is and what can do for you, please review the following materials:

• Aggr() Technical Brief
• HIC bringing some clarity

Despite the fact that we have been able to do some crazy stuff with Aggr(), every time we wanted to explore the possibilities of inter-record or range functions we faced a wall, Aggr() array of values couldn’t be sorted by any means. You couldn’t control how the data was ‘stored’ in the aggr() virtual table (load order by default) making it hardly usable for a variety of cases such as cumulative calculations, inter-record and range calculations, etc.

Aggr syntax used to be like:

    Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, dim{, dimension})

What's new?

From now on, we count in with a brand new Aggr() chart function that allows us to sort the resulting array (both in Qlik Sense 2.2 and QlikView 12*). The magic happens in the new StructuredParameter.

Syntax is now as following:

    Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})

A StructuredParameter is a combination of a dimension, and optionally the all new sorting method.

StructuredParameter syntax is as follow:

     1 StructuredParameter:  (FieldName, (NUMERIC, ASCENDING))

     2 StructuredParameters: (FieldName,(Field2Name,(FREQUENCY,DESCENDING)))

An Aggr() array can contain virtually anything so you have to specify what data type will be sorted within the array of data (numeric, text, frequency or Load_Order) and the sort criteria, basically ASC or DESC. I strongly recommend you to check the Aggr() help page and get familiar with the new syntax.

Real life example

Please read this blog post to discover a real usage scenario.

(...) I needed to capture the percentage and number of the gains and losses in a KPI object(...) In order for the Aggr() function to work as I expected, I needed to make sure the MonthYear field was sorted properly in ascending order so I handled this in my script.  Note that if the MonthYear field was not in ascending order, the Above() function may not always return the previous month thus returning the wrong results.

Now, to solve Jennell's dilemma we could simply use Aggr() avoiding any extra steps such as sorting the data in the script and making our calculation fully dynamic.

    max(aggr(sum(Members)-above(Sum(Members)), (MonthYear,(NUMERIC, ASCENDING))))

I would love to hear usage cases scenarios from you in the comments area. Where you guys plan to use this new capability?.

Enjoy Qliking,

AMZ

*Yes, it also works on QV12. Just ignore the syntax errors you will see in QlikView function editor.

UPDATED: For those of you looking for an expression as sorting parameter, good news, please check out HIC postRecipe for a Pareto Analysis – Revisited

[robert99]

If this does not allow this (sort by like Sum(Value)) isn't this new feature almost as good as useless. Or am I missing the point?

[robert99]

i hope someone can give a real example to explain why it is so awesome. If it can sort by an expression it would be. But if not as I understand things this seems a bit limited. But its hard to say as it is not very well explained.

In other words my initial response is to agree with Jonas and being disappointed

[swuehl]

Robert99,

I would not agree that it's useless.

As discussed in the blog post and the referenced thread, aggr() dimension values are by default sorted by load order. This matters if you use chart inter record functions and you need to sort the virtual table differently, e.g. having a date dimension that should sort in chronological order. A common use case is e.g. a full accumulation of table values, there are dozens of threads that show examples here in the forum.

There are work arounds for the sort limitation, i.e. loading the field values of the dimension used in 'correct' load order.

Nevertheless, I've seen several requests to be able to do this in front end only.

The new feature does introduce a method to sort the dimension values, offering sort criteria TEXT, NUMERIC, LOAD ORDER and FREQUENCY (not sure if I have used this sort criteria ever).

I think there has been a lot of effort made in creating a new syntax, AKA "StructuredParameter" and the now implemented feature already has its relevant use cases.

Nevertheless, I do agree with Jonas (and repeating myself, see my first reply) that Qlik has gone only half way, adding a sort-by-expression would have made this new feature complete.

Maybe there are things we don't know of that make this sort criteria very hard to implement, but I also agree with Jonas that it would have been good to be part of the development process e.g. as beta tester to be able to provide feedback. IMHO, it's kind of strange to announce this new feature / syntax months after QV12 has been released to customers (or I just missed it, maybe).

TLDR: I do not agree that the new feature is useless. I fully agree that the most annoying limitation is still  present.

[robert99]

Thanks swuehl

An excellent explanation. It's Henric like. And it does seem strange that Qlik don't involve the users more. And if there is a reason why 'sort by a formula' is not possible let the users know.

And BTW. I tried to use this new feature and it doesn't work. It just gave me null values.

[swuehl]

Robert,

the new feature will only with QV12 and (latest?) QlikSense, I've shortly tested it and it seems to work as described (but I haven't spent much time on testing yet).

If it doesn't work for you and if you are not using an older version of QV, I would suggest that you create a new thread and post your sample QVW so we can have a look at your issue.

[robert99]

I'm using QV 12 and the latest Sense

But I needed the sort by an expression option. To answer this question. How many different products are sold by customer to achieve 80% of the customer total sales

I used the 80/20 set up. But this use to cause sorting issue in both View  and sense if two dimensions were used. Now sense is fine thank goodness (view still isn't)

It required downloading a table into Excel and loading it back up again. But this is not too much of an issue

[#8]

WOAH! This is fantastic. Removes a TON of overhead on one of our apps.

[rwunderlich]

Aurturo,

Is is correct to say that the sort only works within the aggr? That the aggr does not return a sorted set to an outside aggregation function?

That is:

Concat(aggr(Sum(x), (y(NUMERIC))))

will not return a concatenated string sorted by "y"?

[#10]

Faced that problem today tresesco‌! Would be nice to have this available on front-end

[jolivares]

In the case of Concat you have the sort include in the function:

Concat({[SetExpression] [DISTINCT] [TOTAL [<fld{, fld}>]] string[, delimiter[, sort_weight]])