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

[rwunderlich]

‌Juan, I realize I could use the sort weight. I was only using concat as a way to understand the output order of the aggr. In my real case, I'm trying to use the sortable aggr with irr and the input to irr does not appear to be sorted by aggr.

[ArturoMuñoz]

Rob,

I'm assuming you're right I've performed same test as you did with identical results as yours. I'll try to get an 'official' confirmation that it's working as designed.

[ArturoMuñoz]

@rob I've got confirmation that the sorting just works within the Aggr(). Wrapping it in an outer aggregation function may change the sort order as in your example.

[ecolomer]

Good, Thank's for sharing

Saludos,

Enrique

[dzmitry_shmurye]

It is greate news!

It is possible add two StructuredParameters or use one StructuredParameter and one field for example?

Could I use this feature for getting the next results (text box):

[#7]

I got confused,

I used CONCAT inside my AGGR;

I wanted to CONCAT in a specific order

For instance, if the values 'D' and 'E' had the same value for their StructuredParameter, then I wanted them to be CONATenated in alphabetical order like: D&E, instead of E&D

In fact CONCAT itself takes a sort parameter:         

Concat a field with a sort on expression

[devarasu07]

Hi Arturo,

Hope your fine and doing great!

I'm seeking your advise on below my issue. kindly share your valuable suggestion on this, Thank you very much. ABC Analysis in Qlikview

Note:

I would like similar functionality where i would like to sort by expression. Basically a ORDER BY.

Aggr(sum(sales),(sum(sales),(NUMERIC, ASCENDING)))

This will open up lot of possibilities. One good example is Running Total/cumulative total as Calculated Dimension.

Thanks, Deva

[jmmayoral3]

Thank you very much for this post, Arturo.

I have spent 2 days searching a method to order aggr function and I was about to give up when I have found your post.

You have save a good graph in my dashboard 

[sunny_talwar]

A more advanced sorting based on expression is also available within Aggr() function now

Recipe for a Pareto Analysis – Revisited

[Anonymous]

nice, this is a big deal! Sorting aggr by dimension only was not really helpful