Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The sortable Aggr function is finally here!

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:

 

 

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
56 Comments

Wow!

0 Likes
3,702 Views
Not applicable

Great feature.

0 Likes
3,702 Views
dirk_konings
Contributor III

Great!

0 Likes
3,702 Views
Luminary
Luminary

Great stuff! Thank you.

0 Likes
3,702 Views
Not applicable

Nice! This would have saved me a lot workarounds in the past...

0 Likes
3,702 Views
MVP
MVP

That's great news, Arturo!

I've used aggr() sometimes for accumulations (full or rolling), and I always needed to ensure the right load order for the last aggr() dimension.

It would have been not great, but awesome news if we could use a sort by expression, like Sum(Value).

This would allow to answer questions like "how many customers make 80% of my sales" without having to use triggers, alternate states and pareto select actions.

Two more questions:

- Any reason why there are so many synonyms for the sort order criteria? Just wondering why.

- I noticed that the {SetExpression} now actually seems to work (AFAIR, it hasn't worked in older versions, though it's in the syntax help for ages). That's a very recent change, too, right?

3,702 Views
michael_gardner
Contributor III

Nice!  Can we fix Distinct Load now so it only applies to the specified table?  Today was about the 4th time that I spent at least an hour wondering why my data no longer made since because I joined or concatenated a table that was using Distinct.

0 Likes
3,702 Views
MVP & Luminary
MVP & Luminary

This is going to be really helpful for doing some more advanced Top N tables.  I'm looking forward to putting it into practice.

0 Likes
3,702 Views
MVP & Luminary
MVP & Luminary

Awesome new feature, thanks for sharing!

0 Likes
3,702 Views
Partner
Partner

Finally....

It will solve lot of my problems in ranking, Top, Deciles, etc....

0 Likes
3,702 Views

Thanks

0 Likes
3,702 Views

This is awesome stuff. Thanks for this new introduction.

0 Likes
3,702 Views
MVP
MVP

Great to know that one limitation is limited now! Looking for more - like if we can have an evaluate() for front-end.

Thanks.

3,702 Views
Partner
Partner

This is good stuff, we are looking forward ............

0 Likes
3,702 Views
Partner
Partner

Awsome! Thank you.

0 Likes
3,702 Views
jolivares
Valued Contributor

Hi Arturo, this is great for us, but I try to implement and I don't know what is happening. Why my QV ver 12 does no recognize the instruction :

Capture.PNG

Capture.PNG

Thanks in advance.

0 Likes
3,702 Views
Not applicable

Good news, tank you!

0 Likes
3,702 Views

Juan‌ I think the expression editor is having difficultly in understanding the syntax, but the expression itself should work. I have tested it out and it in fact gives the desired results.

3,702 Views
MVP & Luminary
MVP & Luminary

Arturo did mention that in his blog post:

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

Will this be available in Qlikview 11.2?

0 Likes
3,702 Views
Luminary
Luminary

Great news!

3,702 Views
MVP & Luminary
MVP & Luminary

That is very unlikely. It's implemented in the new QIX engine. I doubt it will be backported to the older engine that's used in Qlikview 11.20.

3,702 Views
Employee
Employee

Any plans about using an expression as sorting parameter?

3,702 Views
Employee
Employee

Olá, Unfortunately I don't know what the plans are for Aggr. This update came out of the blue for me but if I hear/read anything that can be shared I will update the post.

3,702 Views
jonas_rezende
Valued Contributor

Thanks for share.

0 Likes
3,702 Views
Partner
Partner

Excellent news!

0 Likes
3,702 Views
Luminary
Luminary

Use an expression as sort parameter would be a great feature to build ABC Analysis.

For example, create a KPIs objects that show me number of Customer in A, B or C Tier using sum(Sales) descending as Sort Order and Customer ID as dimension.

3,702 Views
Partner
Partner

Wow... Close but no cigar is my first impression from reading this. So, someone has gone through the trouble of revising the Aggr statement - but yet, did not add what was most sorely missing? Just search through the Ideas page and you'll see an idea raised a good six years ago by yours truly quoting a genuine, recurring use case for dynamic sorting which is the humble Pareto calculation. I'm happy to stand corrected, but I can't see any way of benefitting from this configurable static sort for solving that use case. in fact, I struggle to see many use cases at all for this feature unfortunately. Was this released in a Beta version? Did you capture any community feedback on whether this feature enhancement would meet any real world needs? If there is a second development iteration of this feature, please do not hesitate to reach out - I'd happily participate in improving upon this feature. For those of you in the community who wish to see dynamic sorting of the Aggr statement in the future, please follow this link and cast your vote behind the original idea:

/Jonas

3,702 Views
saran7de
Contributor III

Thank you

0 Likes
3,702 Views
Partner
Partner

This is powerful! Thanks

0 Likes
3,702 Views