Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
abyqlik_com
Creator
Creator

The AGGR Function.....

Capture.JPG

I thought I would start this blog post with a simple multiple choice question:

Is AGGR.....

a - Used in many QlikView applications to great effect
b - Mis-used in many QlikView applications
c - Used to return an answer without really knowing why you get the answer
d - Not used at all because we're "not quite sure what is does"
e - All of the above

The answer in my opinion is "e - All of the above".

When I came to write this post and indeed the technical brief, the hardest part of all was actually coming up with a really good, easy to understand description of AGGR, as it has to make sense to both "technical" and "not so technical" people. I finally settled on the one below...

When it is used, the AGGR function produces a virtual table, with one expression and grouped by one or more dimensions. The contents / result of this virtual table can then be used / aggregated by a further outer aggregation function(s).

With this definition in mind, I have produced a Technical Brief and application which can be found here and here. In this document I have tried to provide an overview of the function and provided some examples of where it can be used to great effect.

So, when should you use AGGR? The answer is: Whenever you want to perform an aggregation in two steps. In the technical brief we show you some scenarios like.....

  • Largest average order value for each Salesperson for each Country, where average order value for each Salesperson is the first calculation and the largest in each Country is the second.
  • The Salesperson responsible for the largest average order value in each Country
  • How many Salespeople have average order values of less than $100 in each Country

I would also be interested in any other creative and powerful uses of AGGR you may have.


Thanks.

ABY

19 Comments
cheburashka
Creator III
Creator III

Hello,

I'm still not able to see the docx document.

The response after clicking the link is the following:

" It appears you're not allowed to view what you requested. You might contact your administrator if you think this is a mistake. "

, Thx for your help

0 Likes
2,998 Views
Miguel_Angel_Baeyens

Yes, it's fixed now. Get back to us in case you cannot download it.

Kind regards.

Miguel

2,998 Views
cheburashka
Creator III
Creator III

Solved, thx

0 Likes
2,998 Views
pauljohansson
Creator III
Creator III

Hi Roland,

Lets say you have the dimensions Continent, Country, Office &  measure Sales in a straight table.

Using expression aggr (sum(Sales),Continent) gives you a table showing sales per continent, one row per Continent.

Using aggr (NODISTINCT sum(Sales),Continent) still gives you the sales per continent, however, it will repatedly show the values depending on how may Country & Offices there are.

br

Paul

0 Likes
2,998 Views
cje
Employee
Employee

Hi,

Still can't access to the document.

Forbidden.

Thxs

0 Likes
2,998 Views
mphekin12
Specialist
Specialist

Still getting the following error:

Unauthorized

It appears you're not allowed to view what you requested.  You might contact your administrator if you think this is a mistake.

Thanks

0 Likes
2,998 Views